Reading Selection Values From a File Using WHERE

Instead of typing literal test values in a WHERE phrase, you can store them in a file and refer to the file in the report request. You can then select records based on equality or inequality tests on values stored in the file.

Reference: Usage Notes for Reading Values From a File

In order to read selection criteria from a file, the file must comply with the following rules:

Each value in the file must be on a separate line.
The selection value must start in column one.
The values are assumed to be in character format, unless the file name is HOLD, and numeric digits are converted to internal computational numbers where needed (for example, binary integer).
The file can be approximately 16,000 bytes. If the file is too large, an error message displays.
Alphanumeric values with embedded blanks or any mathematical operator (-, +, *, /) must be enclosed in single quotation marks.
When a compound WHERE phrase references more than one file, the specified files must have the same record formats.
If your list of literals is too large, an error is displayed.
Sets of file names may be used, separated by the word OR or AND. Actual literals may also be mixed with the file names. For example:
```
WHERE fieldname operator (filename) OR fieldname operator literal...
```

Syntax: How to Read Selection Values From a File (WHERE)

WHERE field1 operator1 (file1) [{OR|AND} field2 operator2 (file2) ... ]

where:

field1, field2

Are any valid field names or aliases.

operator1, operator2

Can be the EQ, IS, NE, or IS-NOT operator.

file1, file1

Are the names of the files.

These are the ddnames assigned by a DYNAM or TSO ALLOCATE command for z/OS.

Top of page

Example: Reading Selection Values From a File

The following request against the GGPRODS data source creates a HOLD file named EXPER1 that contains product IDs B141, B142, B143, and B144.

TABLE FILE GGPRODS
BY PRODUCT_ID BY PRODUCT_DESCRIPTION
WHERE PRODUCT_ID EQ 'B141' OR 'B142' OR 'B143' OR 'B144'
ON TABLE HOLD AS EXPER1 FORMAT ALPHA
END

The following request against the GGPRODS data source creates a HOLD file named EXPER2 that contains product IDs B144, F101, and F102.

TABLE FILE GGPRODS
BY PRODUCT_ID BY PRODUCT_DESCRIPTION
WHERE PRODUCT_ID EQ 'B144' OR 'F101' OR 'F102'
ON TABLE HOLD AS EXPER2 FORMAT ALPHA
END

The following request selects the values that exist in both EXPER1 AND EXPER2.

TABLE FILE GGPRODS
SUM PRODUCT_DESCRIPTION
BY PRODUCT_ID
WHERE PRODUCT_ID EQ (EXPER1) AND PRODUCT_ID IS (EXPER2)
ON TABLE SET PAGE NOPAGE
END

The output is:

Product
Code     Product
-------  -------
B144     Kona