Using &NDBSCAN Statements › Scan Expressions

Scan Expressions

A scan request contains a free form text scan expression. This expression contains:

• Fields - for example, SURNAME, DOB
• Values - for example, SMITH, 04/12/58
• Operators - for example, =, NE
• Connectors - for example, AND, OR

Parentheses can be used to group parts of the expression.

The expression can be spread across several NCL statements, using the START/DATA/END syntax, as per the &NDBADD, &NDBUPD, and &NDBFMT statements. This syntax also allows construction of the scan expression. This is especially useful in table-driven systems, for example, the features table.

The expression consists of a number of scan-tests, connected by AND, OR, NOT, and parentheses. Each test can:

• Test a field, or list of fields (including generic and range field names), against a value, list of values, or generic value(s) or against other fields. This is called a field to field compare.
• The comparison can use the standard relational operators, for example, EQ, NE, GT, and =, >, as well as, special operators:
  • PRESENT-to test for presence
  • ABSENT-to test for absence
  • LIKE-to perform a pattern match
  • CONTAINS-to test, for character fields, for one field containing a value, or other field value
• SQL-like capabilities exist that allow nested scans, where field values from the inner scans are used as input to the tests in the outer scan(s).

The following are examples of valid scan expressions:

SURNAME = SMITH AND DOB LT 600101
DESC* CONTAINS MVS

DATECLOSED ABSENT OR DATECLOSED  DATEOPEN PLUS 5
NAME LIKE 'J% SMITH%'
SEX = 'F' AND (STATUS = 'SINGLE' | STATUS = 'DIVORCED')
SEX = 'F' AND STATUS = ANY 'SINGLE', 'DIVORCED'

The last two expressions are equivalent. The parentheses are required in the first of these to bind the OR (|) tests together, as AND takes precedence over OR.