Calling a Function in WHERE or IF Criteria

In this section:

How to:

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: