# SET Statements (DQL)

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

datacom151

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.

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,

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,

Dataquery

moves the value to the result based on the result's precision (the number of digits before and after the decimal point). 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 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.

A SET statement can use the following data types: NUM DEC, BIN, 1/2 BIN, FUL BIN, DBL CHAR, KANJI.

Topics discussed on this page include:

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 columnsNext section.

Numeric Column Syntax Diagram

You can set a temporary result to the value of a numeric column.

Syntax Diagram

The following is the syntax for the SET statement when you want to set the result to a numeric column.

►►─ SET ──┬─────────┬─ =result──────────────────────────►◄ └─ (numeric-column-name) ─┘n.d

The following explains the keywords in the preceding format.

- resultAssigns a name to the temporary result. A result name can be up to 32 alphanumeric characters. The first character of the name must be alphabetic.
- (n.d)Specifies the precision (the specific number of digits before and after the decimal) of the result, overriding the site default precision. (n.d) indicates that the result contains n integers and d decimals. The total of n + d cannot exceed 15 digits.For example, (9.2) specifies a result with nine integer positions and two decimal positions. The total number of digits is 11.
- numeric-column nameIndicates the name of a result in another SET statement or a non-floating-point numeric column defined toDatacom Datadictionaryas part of a database table referenced in your query.The letters N, B and D in theDatacom Datadictionaryreport and the letter N in the TYPE column of the extended column display indicate a numeric column.If the column is from a table, as opposed to a temporary (SET-created) result,Dataqueryassumes it is contained within the last table named in the query. If you want to specify a column within a table other than the last table named, you must qualify the column name by preceding it with the name of the table, for example, PAYROLL ACTIVITY-CODE.Dataqueryassumes that each column specified after this qualifying table name is contained in that table.

Arithmetic Result Syntax Diagram

You can set the value of a temporary result to be the value that results from an arithmetic expression.

Syntax Diagram

The following is the syntax for the SET statement when you want to set the result to equal the value resulting from an arithmetic expression.

►►─ SET ──┬─────────┬─ =result────────────────────────►◄ └─ (arithmetic-expression) ─┘n.d

The following explains the keywords in the preceding format.

- resultAssigns a name to the new result. A result name can be up to 32 alphanumeric characters, with an alphabetic first character.
- (n.d)Specifies the precision (the specific number of digits before and after the decimal) of the result, overriding the default precision. (n.d) indicates that the result contains n integers and d decimals. The total of n + d cannot exceed 18 digits.For example, (9.2) specifies a result with nine integer positions and two decimal positions. The total number of digits is 11.
- arithmetic-expressionSpecifies one or more arithmetic operations to be performed on selected data, including null-valued columns. If a computation is done involving null values, the result is null.Dataqueryevaluates the expression according to the hierarchy of operations. (For more information, see Evaluation of Arithmetic Expressions.)

Sample

This is a sample query which illustrates SET statements using arithmetic expressions.

FIND CAI-SLSHST-REC ROWS WITH SHIP-QTY GTE 200 RELATED BY SLMN-ID TO CAI-ORDERS-REC SET NET-COST (8.2) = CAI-SLSHST-REC SHIP-QTY * UNIT-PRICE SET DISCOUNT (5.2) = NET-COST * DISC-PCT SET DISCOUNT-COST (8.2) = NET-COST - DISCOUNT PRINT FROM CAI-ORDERS-REC ORD-ID FROM CAI-SLSHST-REC ITM-ID SHIP-QTY UNIT-PRICE NET-COST DISCOUNT DISCOUNT-COST

Sample

The CAI-SLSHST-REC table looks like this:

SHIP-QTY | ORD-DT | CUST-ID | SLMN-ID | DISC-PCT | IND-CD | UNIT-PRICE | ITM-ID |

103 | 880117 | QW10000 | SMITH | 0.10 | XZ-234 | 1.25 | CAPS |

400 | 880319 | RM10000 | JONES | 0.10 | AM-789 | 7.00 | MATS |

200 | 880215 | UV9999 | WATT | 0.10 | XZ-744 | 3.00 | LOCKS |

167 | 890117 | RV8009 | ROBB | 0.05 | AA-909 | 2.37 | DIAL |

300 | 890215 | XY3375 | ADAMS | 0.10 | ZM-889 | 5.00 | VISOR |

Sample

The CAI-ORDERS-REC table looks like this:

ORD-ID | ORD-TOT | CUST-PO | SLMN-ID | SHIP-ID | ITM-ID | FRT-TOT | GROUP |

BR950 | 2595.00 | RT900 | JONES | LMN-12-1 | MATS | 75.00 | AMA |

CQ322 | 573.99 | RT907 | WATT | LMN-33-7 | LOCK | 33.99 | AMA |

RR995 | 17.50 | RT899 | SMITH | ALV-01-1 | CAPS | 1.25 | AMA |

AW009 | 1433.00 | RT999 | ADAMS | ARR-33-9 | VISOR | 83.00 | CRA |

Sample

The active found set for the preceding query looks like this:

ORD-ID | ITM-ID | SHIP-QTY | UNIT-PRICE | NET-COST | DISCOUNT | DISCOUNT-COST |

BR950 | MATS | 400 | 7.00 | 2800.00 | 280.00 | 2520.00 |

CQ322 | LOCK | 200 | 3.00 | 600.00 | 60.00 | 540.00 |

AW009 | VISOR | 300 | 5.00 | 1500.00 | 150.00 | 1350.00 |

Arithmetic Expressions in SET

An arithmetic expression can include the following data items:

- Numeric columns from the database
- Results from previous SET statements
- Numeric literals

The following are valid arithmetic operators.

Function | Entry | Description |

Add | + | Adds the specified numeric operands. |

Subtract | - | Subtracts the specified numeric operands. |

Divide | / | Divides the specified numeric operands (divisor cannot be 0). |

Multiply | * | Multiplies the specified numeric operands. |

Parenthetical expression | ( ) | Clarifies which operations are to be performed first in a complex calculation (can specify up to 5 levels of nested parentheses). |

Unary + | + | Indicates that the specified numeric operand is a positive value (must precede the numeric data without a space between the symbol and the data). |

Unary - | - | Indicates that the specified numeric operand is a negative value (must precede the numeric data without a space between the symbol and the data). |

A space must precede and follow each operator. However, no space follows a unary arithmetic operator (an operator accompanying a single value). For example, if your data includes negative numbers, the minus sign (-) in front of a number is the unary operator, such as in -2.

An arithmetic expression can begin only with one of the following:

- Left parenthesis
- Unary +
- Unary -
- Numeric constant
- Numeric column name

An arithmetic expression can end only with one of the following:

- A right parenthesis
- A numeric constant
- A numeric column-name

Evaluation of Arithmetic Expressions

Dataquery

evaluates arithmetic expressions according to the arithmetic operator it finds. This standard processing sequence is called the hierarchy of operations and is performed in the following order.- Expressions within parentheses (innermost parentheses first)
- Expressions within parentheses (same level of parentheses, performing from left to right)
- Mathematical functions
- Unary + and unary -
- * and /
- + and -

Dataquery

evaluates expressions within parentheses first. When expressions are contained within nested parentheses, evaluation proceeds from the least inclusive (innermost) to the most inclusive (outermost) set.When the order of consecutive operations on the same hierarchic level is not completely specified, the order of operation is left-to-right; for example, the following two expressions are evaluated the same.

A + B / C - D / C * F + G * C / H + I A + (B/C) - ((D / C) * F) + ((G * C) / H) + I

There must be a one-to-one correspondence between left and right parentheses in an arithmetic expression.

Sample

The result of an arithmetic expression is dependent on the precision of the result and the arithmetic operation which is performed. The following example illustrates how

Dataquery

calculates the value for FLDX, using the following expression.FLDX (3.3) = ((2. + 3) * (4 + (5 / 6 + (7 - 8)))) + 9

The following chart shows the hierarchy of operations, the part of the expression which is evaluated in each step, and the result of that evaluation. After each step, we show the expression with the value from the just completed step replacing the part that was evaluated.

Step | Part | Result | Comments |

1 | (7 - 8) | -1 | Dataquery evaluates the innermost parenthetical expression first. |

FLDX (3.3) = ((2. + 3) * (4 + (5 / 6 + (-1)))) + 9 | |||

2 | 5 / 6 | 0.833 | Dataquery evaluates the part of the inner parenthetical expression involving division, since division has a higher precedence than addition. |

FLDX (3.3) = ((2. + 3) * (4 + (0.833 + (-1)))) + 9 | |||

3 | (0.833 + (-1)) | -.167 | Dataquery uses the results from the evaluation in Steps 1 and 2 to evaluate the second innermost parenthetical expression. |

FLDX (3.3) = ((2. + 3) * (4 + (-.167))) + 9 | |||

4 | (2 + 3) | 5 | The two remaining inner parenthetical expressions both involve addition and are at the same level, so Dataquery evaluates the left expression first. |

FLDX (3.3) = ((5) * (4 + (-.167))) + 9 | |||

5 | (4 + (-.167)) | 3.833 | Dataquery evaluates the inner parenthetical expression on the right. |

FLDX (3.3) = (5 * 3.833) + 9 | |||

6 | (5 * 3.833) | 19.165 | Dataquery completes the evaluation of the multilevel parenthetical expression by multiplying the values within parentheses of FLDX, (3.3), designates the number of digits Dataquery calculates on either side of the decimal point. The precision (3.3) indicates that the value has three digits to the left and right of the decimal point. |

FLDX (3.3) = 28.165 |

You can also define FLDX with a precision different from (3.3), as in the following alternative formats:

FLDX (7.5) = +0000028.16665 FLDX (2.4) = +28.1665 FLDX (2.0) = +28 FLDX (1.0) = ***E3*** (Display indicates error because of overflow.)

Numeric Literal Syntax Diagram

You can set a temporary result to be a specific numeric value. This is often useful in performing calculations.

Syntax Diagram

The following is the syntax for the SET statement when you want to set the result to a numeric literal value.

►►─ SET ──┬─────────┬─ =result──────────────────────────────►◄ └─ (numeric-literal) ─┘n.d

The following explains the preceding keywords.

- resultAssigns a name to the new result. A result name can be up to 32 alphanumeric characters. The first character of the name must be alphabetic.
- (n.d)Specifies the precision (the specific number of digits before and after the decimal) of the result, overriding the default precision. (n.d) indicates that the result contains n integers and d decimals. The total of n + d cannot exceed 18 digits.For example, (9.2) specifies a result with nine integer positions and two decimal positions. The total number of digits is 11.
- numeric-literalAssigns a numeric constant value to the result. A unary plus (+) or minus (-) sign can precede the value. A numeric-literal can be up to 18 numbers (integers and decimals).
If DECPT=COMMA in the System Option Table, the numeric literal must be enclosed in quotes. See yourNote:DataqueryAdministrator with any questions about system options.

Sample

The name assigned to a result can be used in subsequent statements as shown in the following example.

FIND 10 CAI-SLSHST-REC ROWS WITH SHIP-QTY > 100 RELATED BY SLMN-ID TO CAI-ORDERS-REC SET NET-COST (8.2) = CAI-ORDERS-REC SHIP-QTY * UNIT-PRICE SET DISCOUNT-PCT = .06 SET DISCOUNT (5.2) = NET-COST * DISCOUNT-PCT SET DISCOUNT-COST (8.2) = NET-COST - DISCOUNT PRINT FROM CAI-ORDERS-REC ORD-ID FROM CAI-SLSHST-REC ITM-ID SHIP-QTY UNIT-PRICE NET-COST DISCOUNT DISCOUNT-COST

Mathematical Functions in SET

Dataquery

provides mathematical functions which help to simplify certain arithmetic operations. Two basic types of functions exist, standard and user-defined. Both types work with column values at the row level and not with a mixture of column values and column totals.Standard Functions

Dataquery

provides the following standard functions for use in a SET statement of a query.►►─┬─ MAX() ─┬──────────────────────────────────────────────►◄ ├─ MIN(arg1,arg2,...,argn) ─┤ ├─ AVG(arg1,arg2,...,argn) ─┤ └─ ABS(arg1,arg2,...,argn) ───────────────┘arg1

Description

- MAX(arg1,arg2,...,argn)Returns the highest value of a list of arguments.
- MIN(arg1,arg2,...,argn)Returns the lowest value of a list of arguments.
- AVG(arg1,arg2,...,argn)Returns the average value of a list of arguments.
- ABS(arg1)Returns the absolute value of the argument.

The argument list in parentheses must immediately follow the function name with no intervening blanks. An argument can be a literal value, numeric database column or the result from a previous SET statement.

User-Defined Functions

If you need a specific mathematical function which

Dataquery

does not provide as a standard function, site management can create a user-defined function (UDF) to meet your request.The syntax diagram for the user-defined function follows:

►►─ UDF() ──────────────────────────────────────────────────►◄arg1,arg2,...,argn

The first argument (

*) is a numeric value which represents the number assigned to a particular mathematical function by site management. For example, if 14 functions have been defined,*arg1

*could be 10 and the arguments*arg1

*through*arg2

*would specify result names, column names or values for numeric literals to be used in the specified function.*argn

Sample

This sample SET statement shows the use of a user-defined function as part of a calculation.

SET A = (FLDB * FLDC) / AVG(X,Y,Z) + UDF(7,X,Y,Z)

In the example preceding, 7 represents the number of the mathematical function established by site management that you want to use. X, Y and Z specify the result name, column name or a numeric literal to be used in the mathematical function assigned to 7. The result A equals the result of the calculations performed in the UDF expression and the other expressions in the SET statement.

Using Multiple SET Statements

The SET statement performs a variety of arithmetic operations, allowing you to arrange calculations in several steps. In the following example, a series of SET commands makes use of columns and numeric constants to define a group of simple equations.

SET RATE (5.2) = 10.75 SET VALUE (2.2) = 14.22 SET INTRT (2.5) = 14.34721 SET A (2.5) = AVG(14,9,6) SET B (6.0) = (-VALUE * 100) SET SUBTOT (4.5) = (INTRT + 7) / (-16) SET AMT (4.4) = (RATE / INTRT) + (.6392 * VALUE) SET SUM (4.4) = (RATE + A - B) + (SUBTOT + AMT / 5)

After each SET statement is calculated, its value can then be used in the next calculation until the last result has been evaluated. The following represent the results obtained from the previous calculations.

A = 09.66667 B = -001422 SUBTOT = -0001.33420 AMT = 0009.8387 SUM = 1443.0502

The final result SUM contains the value calculated from the last equation involving values from the other SET statements. Such use of nested or connected SET statements permits you considerable flexibility in performing calculations, especially when the standard operators and functions are employed in conjunction with user-defined functions.

SET Limitations

Following are important limitations to observe when constructing a SET statement.

- Only simple fields may be referenced in a 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 SETs for a query with a FIND on one table and no SORT statement.
- The Found Table record size (768) limits the number of combined SET statements and sort keys that can be used in a query.
- SQL DATE, TIME and TIMESTAMP cannot be used with DQL SET, SORT and WITH.
- A numeric column whose value can be zero should not be used as a divisor in a SET statement or an arithmetic expression.
- The maximum numeric precision is 18 digits.
- An arithmetic expression cannot have more than five levels of nested parentheses.
- A SET statement cannot have more than 10 levels of nested parentheses.