Creating a Conditional Expression

Syntax: How to Write a Conditional Expression

IF expression1 THEN expression2 [ELSE expression3]

where:

expression1

Is the expression that is evaluated to determine whether the field is assigned the value of expression2 or of expression3.

expression2

Is an expression that results in a format compatible with the format assigned to the field. It may be a conditional expression, in which case you must enclose it in parentheses.

expression3

Is an expression that results in a format compatible with the format assigned to the field. Enclosure of the expression in parentheses is optional.

ELSE

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 ...).

Example: Supplying a Value With a Conditional Expression

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

Example: Defining a True or False Condition

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.