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.
WHERE field1 operator1 (file1) [{OR|AND} field2 operator2 (file2) ... ]
where:
Are any valid field names or aliases.
Can be the EQ, IS, NE, or IS-NOT operator.
Are the names of the files.
These are the ddnames assigned by a DYNAM or TSO ALLOCATE command for z/OS.
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
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
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 |