You can select records for your reports using a variety of tests that are implemented using the operators described in Operators Supported for WHERE and IF Tests. You can test for:
| In this section: | Screening on Masked Fields With LIKE and IS |
Use the operators FROM ... TO and NOT-FROM ... TO in order to determine whether field values fall within or outside of a given range. You can use either values or expressions to specify the lower and upper boundaries. Range tests can also be applied on the sort control fields. The range test is specified immediately after the sort phrase. | How to: Specify a Range Test (FROM and TO) Example: |
WHERE [TOTAL] fieldname {FROM|IS-FROM} lower TO upper
WHERE [TOTAL] fieldname NOT-FROM lower TO upper
where:
fieldname
Is any valid field name or alias.
lower
Are numeric or alphanumeric values or expressions that indicate lower boundaries. You may add parentheses around expressions for readability.
upper
Are numeric or alphanumeric values or expressions that indicate upper boundaries. You may add parentheses around expressions for readability.
An example of a range test using expressions as boundaries follows:
WHERE SALES FROM (DEALER_COST * 1.4) TO (DEALER_COST * 2.0)
The following illustrates how you can use the range test NOT-FROM ... TO to display only those records that fall outside of the specified range. In this example, it is all employees whose salaries do not fall in the range between $12,000 and $22,000.
TABLE FILE EMPLOYEE
PRINT CURR_SAL
BY LAST_NAME
WHERE CURR_SAL NOT‑FROM 12000 TO 22000
END
The output is:
The following examples demonstrate how to perform range tests when sorting a field using the BY or ACROSS sort phrases:
BY MONTH FROM 4 TO 8
or
ACROSS MONTH FROM 6 TO 10
The operators GE (greater than or equal to), LE (less than or equal to), GT (greater than), and LT (less than) can be used to specify a range. | How to: | Example: |
GE ... LE enable you to specify values within the range test boundaries.
LT ...GT enable you to specify values outside the range test boundaries.
To select values that fall within a range, use
WHERE fieldname GE lower AND fieldname LE upper
To find records whose values do not fall in a specified range, use
WHERE fieldname LT lower OR fieldname GT upper
where:
fieldname
Is any valid field name or alias.
lower
Are numeric or alphanumeric values or expressions that indicate lower boundaries. You may add parentheses around expressions for readability.
upper
Are numeric or alphanumeric values or expressions that indicate upper boundaries. You may add parentheses around expressions for readability.
This WHERE phrase selects records in which the UNIT value is between 10,000 and 14,000.
WHERE UNITS GE 10000 AND UNITS LE 14000
This example is equivalent to:
WHERE UNITS GE 10000
WHERE UNITS LE 14000
The following illustrates how you can select values that are outside a range of values using the LT and GT operators. In this example, only those employees whose salaries are less than $12,000 and greater than $22,000 are included in the output.
TABLE FILE EMPLOYEE
PRINT CURR_SAL
BY LAST_NAME
WHERE CURR_SAL LT 12000 OR CURR_SAL GT 22000
END
The output is:
When creating report requests, you may want to test for missing data. For this test to be effective, fields that have missing data must have the MISSING attribute set to ON in the Master File. For information on missing data, see Handling Records With Missing Field Values, and the Describing Data manual. | How to: |
{WHERE|IF} fieldname {EQ|IS} MISSING
where:
fieldname
Is any valid field name or alias.
EQ|IS
Are record selection operators. EQ and IS are synonyms.
{WHERE|IF} fieldname {NE|IS-NOT} MISSING
where:
fieldname
Is any valid field name or alias.
NE|IS-NOT
Are record selection operators. NE and IS-NOT are synonyms.
The CONTAINS and OMITS operators test alphanumeric fields when used with WHERE, and both alphanumeric and text fields when used with IF. With CONTAINS, if the characters in the given literal or literals appear anywhere within the characters of the field value, the test is passed.
OMITS is the opposite of CONTAINS; if the characters of the given literal or literals appear anywhere within the characters of the field's value, the test fails.
CONTAINS and OMITS tests are useful when you do not know the exact spelling of a value. As long as you know that a specific string appears within the value, you can retrieve the desired data.
The following examples illustrate several ways to use the CONTAINS and OMITS operators. The field name that is being tested must appear on the left side of the CONTAINS or OMITS operator.
WHERE LAST_NAME CONTAINS 'JOHN'
The LAST_NAME field may contain the characters JOHN anywhere in the field.
WHERE LAST_NAME OMITS 'JOHN'
TABLE FILE EMPLOYEE
LIST LAST_NAME AND FIRST_NAME
WHERE LAST_NAME CONTAINS 'ING'
END
The output is:
LIST LAST_NAME FIRST_NAME
---- --------- ----------
1 BANNING JOHN
2 IRVING JOAN
A mask is an alphanumeric pattern that you supply for comparison to characters in a data field. The data field must have an alphanumeric format (A). You can use the LIKE and NOT LIKE or the IS and IS-NOT operators to perform screening on masked fields. | How to: Screen on Masked Fields (LIKE and NOT LIKE) Screen on Masked Fields (IS and IS-NOT) Reference: | Example: Screening on Initial Characters Screening on Characters Anywhere in a Field |
The wildcard characters for screening on masked fields with:
Note: The LIKE operator is supported in expressions that are used to derive temporary fields with either the DEFINE or the COMPUTE command.
To search for records with the LIKE operator, use
WHERE field LIKE 'mask'
To reject records based on the mask value, use either
WHERE field NOT LIKE 'mask'
or
WHERE NOT field LIKE 'mask'
where:
field
Is any valid field name or alias.
mask
Is an alphanumeric or text character string you supply. There are two wildcard characters that you can use in the mask: the underscore (_) indicates that any character in that position is acceptable; the percent sign (%) allows any following sequence of zero or more characters.
For related information, see Restrictions on Masking Characters.
To search for records with the IS operator, use
{WHERE|IF} field {IS|EQ} 'mask'
To reject records based on the mask value, use
{WHERE|IF} field {IS-NOT|NE} 'mask'
where:
field
Is any valid field name or alias.
IS|IS-NOT
Are record selection operators. EQ is a synonym for IS. NE is a synonym for IS-NOT.
mask
Is an alphanumeric or text character string you supply. The wildcard characters that you can use in the mask are the dollar sign ($) and the combination $*. The dollar sign indicates that any character in that position is acceptable. The $* combination allows any sequence of zero or more characters. The $* is shorthand for writing a sequence of dollar signs to fill the end of the mask without specifying a specific length. This combination can only be used at the end of the mask.
For related information, see Restrictions on Masking Characters.
To list all employees who have taken basic‑level courses, where every basic course begins with the word BASIC, issue the following request:
TABLE FILE EMPLOYEE
PRINT COURSE_NAME COURSE_CODE
BY LAST_NAME BY FIRST_NAME
WHERE COURSE_NAME LIKE 'BASIC%'
END
The output is:
If you want to see which employees have taken a FOCUS course, but you do not know where the word FOCUS appears in the title, bracket the word FOCUS with wildcards (which is equivalent to using the CONTAINS operator):
TABLE FILE EMPLOYEE
PRINT COURSE_NAME COURSE_CODE
BY LAST_NAME BY FIRST_NAME
WHERE COURSE_NAME LIKE '%FOCUS%'
END
The output is:
If you want to list all employees who have taken a 20x‑series course, and you know that all of these courses have the same code except for the final character, issue the following request:
TABLE FILE EMPLOYEE
PRINT COURSE_NAME COURSE_CODE
BY LAST_NAME BY FIRST_NAME
WHERE COURSE_CODE LIKE '20_'
END
The output is:
The following example illustrates how to screen on initial characters and specify the length of the field value you are searching for. In this example, the WHERE phrase states that the last name must begin with BAN and be 7 characters in length (the three initial characters BAN and the 4 placeholders, in this case the dollar sign). The remaining characters in the field (positions 8 through 15) must be blank.
TABLE FILE EMPLOYEE
PRINT LAST_NAME
WHERE LAST_NAME IS 'BAN$$$$'
END
The output is:
To retrieve records with unspecified lengths, use the dollar sign followed by an asterisk ($*):
WHERE LAST_NAME IS 'BAN$*'
This phrase searches for last names that start with the letters BAN, regardless of the name's length. The characters $* reduce typing, and enable you to define a screen mask without knowing the exact length of the field you wish to retrieve.
You can use an escape character in the LIKE syntax to treat the masking characters | How to: Reference: | Example: |
Any single character can be used as an escape character, if prefaced with the word ESCAPE
WHERE fieldname LIKE 'mask' ESCAPE 'c'
where:
fieldname
Is any valid field name or alias to be evaluated in the selection test.
mask
Is the search pattern that you supply. The single quotation marks are required.
c
Is any single character that you identify as the escape character. If you embed the escape character in the mask, before a % or _, the % or _ character is treated as a literal, rather than as a wildcard. The single quotation marks are required.
WHERE field LIKE 'ABCg_' ESCAPE 'g' OR 'ABCg%' OR 'g%ABC'
The VIDEOTR2 data source contains an e-mail address field. To search for the e-mail address with the characters 'handy_' you can issue the following request:
TABLE FILE VIDEOTR2
PRINT CUSTID LASTNAME FIRSTNAME EMAIL
WHERE EMAIL LIKE 'handy_%'
END
Because the underscore character functions as a wildcard character, this request returns two instances, only one of which contains the underscore character.
The output is:
CUSTID LASTNAME FIRSTNAME EMAIL
------ -------- --------- -----
0944 HANDLER EVAN handy_man@usa.com
0944 HANDLER EVAN handyman@usa.com
To retrieve only the instance that contains the underscore character, you must indicate that the underscore should be treated as a normal character, not a wildcard. The following request retrieves only the instance with the underscore character in the e-mail field:
TABLE FILE VIDEOTR2
PRINT CUSTID LASTNAME FIRSTNAME EMAIL
WHERE EMAIL LIKE 'handy\_%' ESCAPE '\'
END
The output is:
CUSTID LASTNAME FIRSTNAME EMAIL
------ -------- --------- -----
0944 HANDLER EVAN handy_man@usa.com
You can test whether instances of a given field in a child segment include or exclude all literals in a list using the INCLUDES and EXCLUDES operators. INCLUDES and EXCLUDES retrieve only parent records. You cannot print or list any field in the same segment as the field specified for the INCLUDES or EXCLUDES test. | Reference: | Example: |
Note: INCLUDES and EXCLUDES work only with multi‑segment FOCUS data sources.
A request that contains the phrase
WHERE JOBCODE INCLUDES A01 OR B01
returns employee records with JOBCODE instances for both A01 and B01, as if you had used AND.
In the following example, for a record to be selected, its JOBCODE field must have values of both A01 and B01:
WHERE JOBCODE INCLUDES A01 AND B01
If either one is missing, the record is not selected for the report.
If the selection criterion is
WHERE JOBCODE EXCLUDES A01 AND B01
every record that does not have both values is selected for the report.
In the CAR data source, only England produces Jaguars and Jensens, and so the request
TABLE FILE CAR
PRINT COUNTRY
WHERE CAR INCLUDES JAGUAR AND JENSEN
END
generates this output:
