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:
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 z/OS, 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.
WHERE [NOT] fieldname IN FILE file
where:
This is the ddname assigned by a DYNAM or TSO ALLOCATE command for z/OS.
For related information, see Usage Notes for Reading Values From a File.
IF fieldname operator (file) [OR (file) ... ]
where:
This is the ddname assigned by a DYNAM or TSO ALLOCATE command for z/OS.
In order to read selection criteria from a file, the file must comply with the following rules:
For IF, more information can appear on a line, but only the first data value encountered on the line is used.
If your list of literals is too large, an error is displayed.
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
WebFOCUS |