WHERE Screening Statements

WHERE phrases perform the same simple selection tests as IF phrases, but also allow for complex criteria and tests to be used directly in TABLE requests without using the DEFINE command. The basic syntax is: WHERE criteria, with the criteria being any valid Boolean (true or false) expression. Below are some examples of complex screening statements using WHERE. Note that alphanumeric and date values must always be enclosed in single quotes when using the WHERE command.

A single WHERE phrase can be used with multiple criteria connected by AND. This is similar to writing multiple IF statements. In the example below, the parenthesis were added for clarity and are not required.

WHERE (AN:CBID EQ 'R03') AND (AN:TIMEBASE EQ 'FT') AND (AN:SEX EQ 'F')

WHERE phrases can be used for field-to-field comparisons. For example, to identify employees whose transaction effective date is equal to their employment date.

WHERE AC:EFFDATE EQ AC:EMPDATE

Arithmetic operations (+, -, *, /, **) can be used in WHERE screening statements. For example, to identify employees whose hourly pay is greater than $25.00. In the example below, the parenthesis are required to control the order of execution.

WHERE (AC:BASEPAY/173.33) GT 25

WHERE allows compound logical expression involving AND or OR conditions. For example, to identify employees whose transaction is S42, or those whose transaction is 645 and a separation code value of 'T'.

WHERE (TR:TRANCODE EQ 'S42') OR (TR:TRANCODE EQ '645' AND TR:SEPCODE EQ 'T')

To learn more about WHERE selection tests, refer to your FOCUS documentation.