Using Operators in Record Selection Tests

Reference:

You can include a variety of operators in your WHERE and IF selection tests. Many of the operators are common for WHERE and IF. However, several are supported only for WHERE tests.

Reference: Operators Supported for WHERE and IF Tests

You can define WHERE and IF selection criteria using the following operators.

WHERE Operator

IF Operator

Meaning

EQ
IS
EQ
IS

Tests for and selects values equal to the test expression.

NE
IS-NOT
NE
IS-NOT

Tests for and selects values not equal to the test expression.

GE
GE
FROM
IS-FROM

Tests for and selects values greater than or equal to the test value (based on the characters 0 to 9 for numeric values, A to Z and a to z for alphanumeric values).

The test value can be a field value or the result of an expression.

GT 
EXCEEDS 
IS-MORE-THAN
GT 
EXCEEDS 
IS-MORE-THAN

Tests for and selects values greater than the test value.

LT 
IS-LESS-THAN 
LT 
IS-LESS-THAN 

Tests for and selects values less than the test value.

LE
LE 
TO

Tests for and selects values less than or equal to the test value.

GE lower AND 
... 
LE upper 
 

Tests for and selects values within a range of values.

LT lower OR 
... GT upper  
 

Tests for and selects values outside of a range of values.

FROM lower 
TO upper
 

Tests for and selects values within a range of values.

IS-FROM lower 
TO upper
IS-FROM lower 
TO upper 

Tests for and selects values within a range of values. For WHERE, this is alternate syntax for FROM lower to UPPER. Both operators produce identical results.

NOT-FROM lower 
TO upper
NOT-FROM lower 
TO upper 

Tests for and selects values that are outside a range of values.

IS MISSING 
IS-NOT MISSING 
NE MISSING
IS MISSING 
IS-NOT MISSING 
NE MISSING

Tests whether a field contains missing values. If some instances of the field contain no data, they have missing data. For information on missing data, see Handling Records With Missing Field Values.

CONTAINS 
LIKE
CONTAINS 
LIKE

Tests for and selects values that include a character string matching test value. The string can occur in any position in the value being tested. When used with WHERE, CONTAINS can test alphanumeric fields. When used with IF, it can test both alphanumeric and text fields.

OMITS 
NOT LIKE 
OMITS 
UNLIKE

Tests for and selects values that do not include a character string matching test value. The string cannot occur in any position in the value being tested. When used with WHERE, OMITS can test alphanumeric fields. When used with IF, it can test both alphanumeric and text fields.

INCLUDES 
INCLUDES

Tests whether a chain of values of a given field in a child segment includes all of a list of literals.

EXCLUDES 
EXCLUDES

Tests whether a chain of values of a given field in a child segment excludes all of a list of literals.

IN (z,x,y)
 

Selects records based on values found in an unordered list.

NOT ... IN 
(z,x,y) 
 

Selects records based on values not found in an unordered list.

IN FILE 
 

Selects records based on values stored in a sequential file.

NOT ... IN FILE 
 

Selects records with field values not found in a sequential file.

IF-THEN-ELSE
 

Selects records based on the logical conditions listed in the IF-THEN-ELSE phrase.

Example: Using Operators to Compare a Field to One or More Values

The following examples illustrate field selection criteria that use one or more values. You may use the operators: EQ, IS, IS-NOT, EXCEEDS, IS-LESS-THAN, and IN.

Example 1: The field LAST_NAME must equal the value JONES:

WHERE LAST_NAME EQ 'JONES'

Example 2: The field LAST_NAME begins with 'CR' or 'MC:'

WHERE EDIT (LAST_NAME, '99') EQ 'CR' OR 'MC'

Example 3: The field AREA must not equal the value EAST or WEST:

WHERE AREA IS-NOT 'EAST' OR 'WEST'

Example 4: The value of the field AREA must equal the value of the field REGION:

WHERE AREA EQ REGION

Note that you cannot compare one field to another in an IF test.

Example 5: The ratio between retail cost and dealer cost must be greater than 1.25:

WHERE RETAIL_COST/DEALER_COST GT 1.25

Example 6: The field UNITS must be equal to or less than the value 50, and AREA must not be equal to either NORTH EAST or WEST. Note the use of single quotation marks around NORTH EAST. All alphanumeric strings must be enclosed within single quotation marks.

WHERE UNITS LE 50 WHERE AREA IS-NOT 'NORTH EAST' OR 'WEST'

Example 7: The value of AMOUNT must be greater than 40:

WHERE AMOUNT EXCEEDS 40

Example 8: The value of AMOUNT must be less than 50:

WHERE AMOUNT IS-LESS-THAN 50

Example 9: The value of SALES must be equal to one of the numeric values in the unordered list. Use commas or blanks to separate the list values.

WHERE SALES IN (43000,12000,13000)

Example 10: The value of CAR must be equal to one of the alphanumeric values in the unordered list. Single quotation marks must enclose alphanumeric list values.

WHERE CAR IN ('JENSEN','JAGUAR')

Example: Using IF-THEN-ELSE Logic in a WHERE Clause

The following request uses IF-THEN-ELSE logic in a WHERE clause to select records based on values of WHOLESALEPR where the values used for selection vary depending on the value of LISTPR in that record.

TABLE FILE MOVIES
PRINT COPIES
LISTPR
WHOLESALEPR
BY CATEGORY
WHERE WHOLESALEPR GT (IF LISTPR GT 20.00 THEN 15.00 ELSE 11.00)
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
GRID=OFF,$
ENDSTYLE
END

The output is shown in the following image. In the selected records, WHOLESALEPR is greater than $15.00 if LISTPR is greater than $20.00. WHOLESALEPR is greater than $11.00 if LISTPR is less than or equal to $20.00.

Example: Using Variables in Record Selection Tests

In this example, the field REGION is used in the WHERE test as a variable so that when the report is executed, the user is prompted to select one of the listed values (CE, CORP, NE, SE or WE) of the REGION field. The text that appears after the values is what appears before the drop-down list in the output.

TABLE FILE EMPDATA
SUM SALARY
BY DIV
BY DEPT
HEADING
"Current Salary Report"
"for the &REGION Division"
" "
WHERE ( DIV EQ
'&REGION.(CE,CORP,NE,SE,WE).Please select a Region.');
END

The output is:

Select a region from the drop-down list and click Submit. The output for the NE region is: