Calling a Function in WHERE or IF Criteria

You can call a function in WHERE or IF criteria. When you do this, the output value of the function is compared against a test value.

Call a Function in WHERE Criteria

WHERE function relation expression 

where:

function

Is a function.

relation

Is an operator that determines the relationship between the function and expression, for example, EQ or LE.

expression

Is a constant, field, or function. A literal must be enclosed in single quotation marks.

Call a Function in IF Criteria

IF function relation value 

where:

function

Is a function.

relation

Is an operator that determines the relationship between the function and expression, for example, EQ or LE.

value

Is a constant. In a DEFINE or COMPUTE command, the value must be enclosed in single quotation marks.

Calling a Function in WHERE Criteria

The SUBSTR function extracts the first two characters of LAST_NAME as a substring, and the request prints an employee's name and salary if the substring is MC. 

TABLE FILE EMPLOYEE
PRINT FIRST_NAME LAST_NAME CURR_SAL
WHERE SUBSTR(15, LAST_NAME, 1, 2, 2, 'A2') IS 'MC'; 
END

The output is:

FIRST_NAME  LAST_NAME          CURR_SAL
----------  ---------          --------
JOHN        MCCOY            $18,480.00
ROGER       MCKNIGHT         $16,100.00

Using a Calculation or Compound IF Command

You must specify the format of the output value in a calculation or compound IF command. There are two ways to do this:

  • Pre-define the format within a separate command. In the following example, the AMOUNT field is pre-defined with the format D8.2 and the function returns a value to the output field AMOUNT. The IF command tests the value of AMOUNT and stores the result in the calculated value, AMOUNT_FLAG.
    COMPUTE
AMOUNT/D8.2 =;
AMOUNT_FLAG/A5 = IF function(input1, input2, AMOUNT) GE 500
   THEN 'LARGE' ELSE 'SMALL';
  • Supply the format as the last argument in the function call. In the following example, the command tests the returned value directly. This is possible because the function defines the format of the returned value (D8.2). 
    DEFINE
AMOUNT_FLAG/A5 = IF function(input1, input2, 'D8.2') GE 500
   THEN 'LARGE' ELSE 'SMALL';