Using Operators in Record Selection 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: