SET Statements

Purpose

You can use the SET statement in a query to do the following:

Perform arithmetic calculations on the data found by a query.
Access user-defined functions.
Perform calculations which make use of values from database tables and from the result created by other SET statements.

Operation

A SET statement must immediately follow the FIND statement or another SET statement. To determine how many SETs can be in a query, use the following formula:

(18 x Number of Tables Joined) + (Length of SORT key)
+ (17 x Number of SET Statements) + 6
= (A Number That is Less Than 760)

There is an absolute maximum of 43 SET statements for a query with a FIND on one table and no SORT statement.

When you use a SET statement in a query, you can create a temporary result and assign it a name and a value. You then can use the result during sorting or reporting in the same way you use a database column.

When a result is first named in a SET statement, CA Dataquery assigns it certain characteristics or attributes. Initially, it is numeric and allows 13 digits, a decimal point, two decimal places, and a plus or minus sign. The default precision can be changed by means of the precision (n.d) entry. The initial value of a result is zero.

After establishing the final value of the SET, CA Dataquery moves the value to the result based on the result's precision (the number of digits before and after the decimal point). CA Dataquery then rounds and truncates the decimals to comply with the precision. If the final value is too large for the result, an overflow can result in an error when CA Dataquery processes the PRINT or DISPLAY statement.

To create a temporary result with SET, specify the desired name and assign a value to that name. The value to which you set the result can be a value of another numeric column, the result of one or more arithmetic operations, a specific numeric value, or any combination of these.

Syntax Diagram

The following shows the syntax of the SET statement.

►►─ SET ─ result ─┬─────────┬─┬─ numeric-column-name ───┬─────────────────────►◄
                  └─ (n.d) ─┘ ├─ arithmetic-expression ─┤
                              └─ numeric-literal ───────┘

Repeat statement once for each result wanted.

The following sections discuss each option of the SET statement.

Numeric columns: Next section.
Arithmetic expressions: Page Arithmetic Result Syntax Diagram.
Numeric literals: Section Numeric Literal Syntax Diagram.