Reading Selection Values From a File

How to:

Reference:

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.

This method has the following advantages:

Syntax: How to Read Selection Values From a File: WHERE field IN file 

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.

Two-part names (app/file) are not supported. The file name is the ddname assigned by a DYNAM or TSO ALLOCATE command for z/OS, or a FILEDEF command for other environments.

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

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

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.

Two-part names (app/file) are not supported. The file name is the ddname assigned by a DYNAM or TSO ALLOCATE command for z/OS, or a FILEDEF command for other environments.

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.

Two-part names (app/file) are not supported. The file name is the ddname assigned by a DYNAM or TSO ALLOCATE command for z/OS, or a FILEDEF command for other environments.

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, and with WHERE, AND. The file names cannot be prefaced with app names. 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 field IN file)

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

This request uses selection criteria from the file EXPER. You must allocate or FILEDEF the EXPER file prior to running the request. For example, if the file is named exper.ftm and it is in the baseapp application, you can issue the following FILEDEF command: 

FILEDEF EXPER DISK baseapp/exper.ftm

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 With WHERE field operator (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

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