Reading Selection Values From a File

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

WHERE [NOT] fieldname IN FILE file

where:

fieldname: Is the name of the selection field. It can be any real or temporary field in the data source.
file: Is the name of the file.
This is the ddname assigned by a DYNAM or TSO ALLOCATE command for z/OS.

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

IF fieldname operator (file) [OR (file) ... ]

where:

fieldname: Is any valid field name or alias.
operator: Is the EQ, IS, NE, or IS-NOT operator (see Operators Supported for WHERE and IF Tests).
file: Is the name of the file.
This is the ddname assigned by a DYNAM or TSO ALLOCATE command for z/OS.

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.
For IF, more information can appear on a line, but only the first data value encountered on the line is used.
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 maximum number of values is 32,767.
For WHERE, alphanumeric values with embedded blanks or any mathematical operator (-, +, *, /) must be enclosed in single quotation marks.
For WHERE, when a compound WHERE phrase uses IN FILE more than once, the specified files must have the same record formats.
If your list of literals is too large, an error is displayed.
For IF, sets of file names may be used, separated by the word OR. Actual literals may also be mixed with the file names. For example:
```
IF fieldname operator (filename) OR literal...etc...
```

Create a file named EXPER, which contains the values B141 and B142.

This request uses selection criteria from the file EXPER. All records for which PRODUCT_ID has a value of B141 or B142 are selected:

TABLE FILE GGPRODS
SUM UNIT_PRICE
BY PRODUCT_DESCRIPTION
WHERE PRODUCT_ID IN FILE EXPER
END

If you include the selection criteria directly in the request, the WHERE phrase specifies the values explicitly:

WHERE PRODUCT_DESCRIPTION EQ 'B141' or 'B142'

The output is:

                     Unit 
Product              Price
-------              -----
French Roast         81.00
Hazelnut             58.00

Create a file named EXPER, which contains the values B141 and B142.

This request uses selection criteria from the file EXPER. All records for which PRODUCT_ID has a value of B141 or B142 are selected:

TABLE FILE GGPRODS
SUM UNIT_PRICE
BY PRODUCT_DESCRIPTION
IF PRODUCT_ID IS (EXPER)
END

If you include the selection criteria directly in the request, the IF phrase specifies the values explicitly:

IF PRODUCT_DESCRIPTION EQ 'B141' or 'B142'

The output is:

                     Unit 
Product              Price
-------              -----
French Roast         81.00
Hazelnut             58.00