A conditional expression assigns a value based on the result of a logical expression. The assigned value can be numeric or alphanumeric.
Note: Unlike selection criteria using IF, all alphanumeric values in conditional expressions must be enclosed in single quotation marks. For example, IF COUNTRY EQ 'ENGLAND'.
IF expression1 THEN expression2 [ELSE expression3]
Is optional, along with expression3. However, if you do not specify an ELSE condition and the IF condition is not met, the value is taken from the last evaluated condition. Therefore, the results may not be what you expect if you do not include an ELSE condition.
Note that the final sorted report may display mixed values. This depends on whether a DEFINE or a COMPUTE is used, and if a data record is evaluated before or after aggregation.
The expressions following THEN and ELSE must result in a format that is compatible with the format assigned to the field. Each of these expressions may itself be a conditional expression. However, the expression following IF may not be an IF ... THEN ... ELSE expression (for example, IF ... IF ...).
The following example uses a conditional expression to assign the value NONE to the field BANK_NAME when it is missing a data value (that is, when the field has no data in the data source):
DEFINE FILE EMPLOYEE BANK_NAME/A20 = IF BANK_NAME EQ ' ' THEN 'NONE' ELSE BANK_NAME; END TABLE FILE EMPLOYEE PRINT CURR_SAL AND BANK_NAME BY EMP_ID BY BANK_ACCT END
The output is:
EMP_ID BANK_ACCT CURR_SAL BANK_NAME ------ --------- -------- --------- 071382660 $11,000.00 NONE 112847612 $13,200.00 NONE 117593129 40950036 $18,480.00 STATE 119265415 $9,500.00 NONE 119329144 160633 $29,700.00 BEST BANK 123764317 819000702 $26,862.00 ASSOCIATED 126724188 $21,120.00 NONE 219984371 $18,480.00 NONE 326179357 122850108 $21,780.00 ASSOCIATED 451123478 136500120 $16,100.00 ASSOCIATED 543729165 $9,000.00 NONE 818692173 163800144 $27,062.00 BANK ASSOCIATION
You can define a true or false condition and then test it to control report output. The following example assigns the value TRUE to the field MYTEST if either of the relational expressions in parentheses is true. It then tests the value of MYTEST:
DEFINE FILE EMPLOYEE MYTEST= (CURR_SAL GE 11000) OR (DEPARTMENT EQ 'MIS'); END TABLE FILE EMPLOYEE PRINT CURR_SAL AND DEPARTMENT BY EMP_ID IF MYTEST IS TRUE END
The output is:
EMP_ID CURR_SAL DEPARTMENT ------ -------- ---------- 071382660 $11,000.00 PRODUCTION 112847612 $13,200.00 MIS 117593129 $18,480.00 MIS 119329144 $29,700.00 PRODUCTION 123764317 $26,862.00 PRODUCTION 126724188 $21,120.00 PRODUCTION 219984371 $18,480.00 MIS 326179357 $21,780.00 MIS 451123478 $16,100.00 PRODUCTION 543729165 $9,000.00 MIS 818692173 $27,062.00 MIS
Note: Testing for a TRUE or FALSE condition is valid only with the IF command. It is not valid with WHERE.