Example 5: Dynamically selecting records

This example shows how to select records by specifying multiple fields on the Open Query File (OPNQRYF) command.

Assume that you want to process all records in which either the Amt field is equal to zero, or the Lstdat field (YYMMDD order in character format) is equal to or less than 88-12-31. You can specify:

OVRDBF     FILE(FILEA) SHARE(*YES)
OPNQRYF    FILE(FILEA) QRYSLT('AMT *EQ 0 *OR LSTDAT +
                  *LE "88-12-31" ')
CALL       PGM(PGMC)
CLOF       OPNID(FILEA)
DLTOVR     FILE(FILEA)

This example also works if the LSTDAT field has a date data type. The LSTDAT field can be in any valid date format; however, the job default date format must be YYMMDD and the job default date separator must be the dash (–).

Note: For any character representation of a date in one of the following formats, MMDDYY, DDMMYY, YYMMDD, or Julian, the job default date format and separator must be the same to be recognized.

If variables are used, the QRYSLT parameter is typed as follows:

QRYSLT('AMT *EQ ' *CAT &VARAMT *CAT ' *OR +
   LSTDAT *LE "' *CAT &VARDAT *CAT '"')

or, typed in reverse order:

QRYSLT('"' *CAT &VARDAT *CAT '" *GT LSTDAT *OR ' +
   *CAT &VARAMT *CAT ' *EQ AMT')

Note: The &VARAMT variable must be defined as a character type. If the variable is passed to your control language (CL) program as a numeric type, you must convert it to a character type to allow concatenation. You can use the Change Variable (CHGVAR) command to do this conversion.