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 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.
  • result
    Assigns 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 name
    Indicates the name of a result in another SET statement or a non-floating-point numeric column defined to
    Datacom Datadictionary
    as part of a database table referenced in your query.
    The letters N, B and D in the
    Datacom Datadictionary
    report 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,
    Dataquery
    assumes 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.
    Dataquery
    assumes 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.
  • result
    Assigns 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-expression
    Specifies 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.
    Dataquery
    evaluates 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.
  1. Expressions within parentheses (innermost parentheses first)
  2. Expressions within parentheses (same level of parentheses, performing from left to right)
  3. Mathematical functions
  4. Unary + and unary -
  5. * and /
  6. + 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.
  • result
    Assigns 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-literal
    Assigns 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).
    Note:
    If DECPT=COMMA in the System Option Table, the numeric literal must be enclosed in quotes. See your
    Dataquery
    Administrator 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(
arg1,arg2,...,argn
) ─┬──────────────────────────────────────────────►◄    ├─ MIN(
arg1,arg2,...,argn
) ─┤    ├─ AVG(
arg1,arg2,...,argn
) ─┤    └─ ABS(
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 (
arg1
) 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
arg2
through
argn
would specify result names, column names or values for numeric literals to be used in the specified function.
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.