Reading Selection Values From a File

Instead of typing literal test values in a WHERE or IF 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.

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

Reference: Usage Notes for Reading Values From a File Example: Reading Selection Values From a File (WHERE) Reading Selection Values From a File (IF)

This method has the following advantages:

• You can save time by coding a large set of selection values once, then using these values as a set in as many report requests as you wish. You also ensure consistency by maintaining the criteria in just one location.
• If the selection values already exist in a data source, you can quickly create a file of selection values by generating a report and saving the output in a HOLD or SAVE file. You can then read selection values from that file.

If you use a HOLD file, it must either be in BINARY format (the default) or in ALPHA (simple character) format; if you use a SAVE file, it must be in ALPHA format (the default). You can also use a SAVB file if the selection values are alphanumeric. For information on HOLD and SAVE files, see Saving and Reusing Your Report Output.

Note that in MVS, a HOLD file in BINARY format that is used for selection values must be allocated to ddname HOLD (the default); the other extract files used for this purpose can be allocated to any ddname.

• You can include entries with mixed-case and special characters.

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.

For MVS, this is the ddname assigned by a DYNAM or TSO ALLOCATE command. On CMS, the ddname is assigned by a FILEDEF command.

For related information, see Usage Notes for Reading Values From a File.

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.

For MVS, this is the ddname assigned by a DYNAM or TSO ALLOCATE command.

For CMS, this is the ddname assigned by a FILEDEF command.

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).
• For IF, the total of all files can be up to 32,767 literals, including new line and other formatting characters. Lower limits apply to fixed sequential and other non-relational data sources.
• For WHERE, the file can be approximately 16,000 bytes. If the file is too large, an error message displays.
• 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...

Example: Reading Selection Values From a File (WHERE)

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

Example: Reading Selection Values From a File (IF)

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

The value of PRODUCT_ID is compared to the values in the EXPER file for the selection criterion.