NOT.IN

The NOT.IN selection clause operator tests whether the content of a field is not in a named select list.

Format

field NOT.IN {NO.CASE} listname

field NOT.IN {NO.CASE} "filename id"

where

field

is the field or evaluated expression to be compared.

listname

is the name of an item previously saved to the $SAVEDLISTS file that holds a field mark delimited list of unacceptable values for field. The optional NO.CASE qualifier causes a case insensitive comparison to be applied. The list name should be enclosed in quotes if it may clash with a dictionary or VOC item name.

filename

is the name of a file containing a record that holds a field mark delimited list of unacceptable values for field. The optional NO.CASE qualifier causes a case insensitive comparison to be applied. The quotes around the filename and id are mandatory.

id	is the record id of the record in filename to be processed.

The NOT.IN selection clause operator causes the report output to contain only records where the content of field is not in the list identified by listname. When applied field is multivalued, at least one value must meet the condition. The EVERY qualifier can be used to test that all values are not in the list.

Example

SELECT COUNTRIES SAVING SHORTCODE

SAVE.LIST CTRY

This sequence of commands processes the COUNTRIES file to build a select list of country codes that it then saves as CTRY. We could then go on to do

LIST CLIENTS WITH COUNTRY NOT.IN CTRY

to list only CLIENTS file records that had an invalid country code.