Calling a Function in WHERE or IF Criteria

In this section:

Using a Calculation or Compound IF Command

How to:

Call a Function in WHERE Criteria
Call a Function in 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.

Syntax: How to 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.

Syntax: How to 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.

Example: 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';
```