Using Operators in Record Selection Tests
 Reference: Operators Supported for WHERE and IF Tests

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.

 Top of page
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

```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) ```
` `

`IN FILE `
` `

Selects records based on values stored in a sequential file.

`NOT ... IN FILE `
` `

 Top of page
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')`
 Top of page
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