Using OPNQRYF

The Open Query File (OPNQRYF) command creates a temporary open data path "on the fly," according to your specifications. Think of it as creating a logical file for you to use once and delete afterwards, but without the trouble of coding DDS for it and without the overhead of the Create Logical File (CRTLF) and DLTF Delete File (DLTF) commands.

The OPNQRYF command has enough parameters to deserve a book of its own. This book will only describe a small fraction of its capabilities. This information should be enough, however, to get you started using OPNQRYF. The first parameter is FILE. You have to give the name of the physical or logical file you want to sort. You can (optionally) include the library name and the name of the member you want to process. For example:

      OPNQRYF FILE((*LIBL/CUSTOMER)) 

This command sorts file CUSTOMER, using the library list to find it. You can omit the *LIBL qualifier and the library list would still be used.

Sorting Records

To indicate how you want to sort the records, use the Key Field (KEYFLD) parameter. In KEYFLD, you can name up to 50 fields you want to use as key fields for the sorting. Any of them can be in descending sequence if you specify *DESCEND. If the field is numeric, you can ignore its sign (positive or negative) by specifying *ABSVAL. For example:

      KEYFLD((ITWHSE) (ITITEM)) 

This parameter specifies that the file should be sorted first by ITWHSE. If more than one record has the same value in ITWHSE, those should be sorted by ITITEM. Both will be sorted in ascending sequence.

      KEYFLD((ITTVAL *DESCEND)) 

Using this parameter, the file is sorted in descending value by ITTVAL.

      KEYFLD((ITWHSE) (ITITEM) (ITTVAL *ABSVAL)) 

This version combines the two. First, sort by ITWHSE in ascending sequence. If several records have the same value in ITWHSE, sort them by ITITEM, then by ITTVAL, ignoring the sign of the numeric value (which means that –3 and +3 are considered equal, and that –4 is considered greater than +3).

The KEYFLD can also have special values *NONE (do not sort the records in any particular order) and *FILE (use the same order in which the records appear in the original file).

Selecting and Omitting Records

OPNQRYF indicates the selection/omission of records using the Query Select (QRYSLT) parameter. QRYSLT must contain a character string that describes a condition that can be either true or false. If the condition is a true value for a specific record, the record is included in the access path built by OPNQRYF. If the condition is a false value, it's omitted. For example, suppose you have an Accounts Receivable file (ACTRCV) and you want to extract only open invoices dated December 31, 1999 or before. The invoice is considered open if field ARSTAT contains an ‘O,’ and the date is contained in field ARDATE in YYYYMMDD format. Here is how you code it:

      QRYSLT('ARSTAT *EQ "O" *AND ARDATE *LE 19991231') 

First, note that the entire expression is contained within single quotes. This makes the expression a character string, as OPNQRYF requires. Next, note that, because you need to compare ARSTAT for equality with the letter ‘O’, the letter ‘O’ must be enclosed in quotes. Because the entire string is already enclosed in quotes, the inner quotes must be doubled. Note the double quotes around the letter ‘O.’

ARDATE, on the other hand, is being compared against a numeric value, so 19991231 does not have any quotes around it. Finally, notice that the expression uses the same operators (*EQ and *AND) used in CL. You can also use symbolic operators (= and &) if you prefer.

QRYSLT in CL Programs

Under normal circumstances, you would not execute OPNQRYF manually from the keyboard, but from within a CL program. In that case, you frequently have to code the QRYSLT parameter in such a way that the values used for comparison are contained in CL variables instead of being constants. For example, you might have coded the following line in your CL program:

      QRYSLT('ARSTAT *EQ "O" *AND ARDATE *EQ 19991231') 

Then you might want to give your CL program some flexibility by letting it compare ARSTAT to values other than ‘O’ and ARDATE to dates other than 19991231. Using variables is natural, so you may be tempted to code:

      QRYSLT('ARSTAT *EQ &STAT *AND ARDATE *EQ &DATE') 

This line, however, is invalid. OPNQRYF will attempt to compare field ARSTAT against constant ‘&STAT’ and it will not be able to compare ARDATE against constant ‘&DATE’ because ARDATE is numeric and ‘&DATE’ is character. The reason this command doesn't work is because the entire string is contained in single quotes, and it is being interpreted as-is, without recognizing &STAT and &DATE as variables. The string mistakes the variables for character constants.

To solve this problem, you need to break up the QRYSLT parameter value into pieces and concatenate them with the *CAT, *BCAT, and *TCAT CL operators, as follows:

      QRYSLT('ARSTAT *EQ "' *TCAT &STAT *TCAT "' *AND ARDATE +      *EQ' *BCAT &DATE) 

To understand this expression, let's look at one piece at a time. First, there is the constant ‘ARSTAT *EQ ’’’. This string ends by including a single quote, so that the system will see it as:

      ARSTAT *EQ ' 

Next, the *TCAT operator concatenates the value contained in &STAT without any intervening spaces. Assuming that &STAT contains a letter O, the system now sees:

      ARSTAT *EQ 'O 

You concatenate, again without any intervening spaces, the following piece, which is ’’’ *AND ARDATE *EQ’. The system now sees:

      ARSTAT *EQ 'O' *AND ARDATE *EQ 

Finally, you concatenate (with one intervening blank, as mandated by *BCAT) the value in character variable &DATE. The date must be contained in a character variable (even though dates are usually numeric) because the concatenation operators require character variables to work. Assuming that variable &DATE contains the value 19991231, the system finally sees:

      ARSTAT *EQ 'O' *AND ARDATE *EQ 19991231 

This is a valid expression the system can evaluate as true or false.

To use OPNQRYF in your application programs, you must do the following:

• Override the file being sorted (the file you named in the FILE parameter) with the Override Database File (OVRDBF) command, specifying SHARE(YES).

• Run the OPNQRYF command as required.

• Call the program that uses the sorted file. This program must reference the original file (as named in the FILE parameter) and declare it as having keyed access.

• Close the file using the CLOF command.

• Delete the override using the DLTOVER command.

Tip 

Always specify the ALLWCPYDTA(*OPTIMIZE) parameter of the OPNQRYF command. This option allows the system to decide what the best option is for sequencing the data.

An example of executing the OPNQRYF is shown in Figure 28.1.

Figure 28.1: Fragments of a CL program using OPNQRYF to sequence data prior to running an HLL program.