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:


Top of page

x
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.


Top of page

x
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.


Top of page

x
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.


Top of page

x
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:



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. 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

WebFOCUS