A: There's more than one method to address your problem. Here's an easy one. Create a work file with one field in it. Make the field the same size and type as the date field you're using for record selection. Change the query to include this file. In the join criteria, join this file to the file containing the production data over common date fields. If the join is an inner join (i.e., the join type is 1, Matched records), add it anywhere to the list of files. If the join is an outer join (join type 2, Matched records with primary file), make sure this new file is the primary file. Remove the record selection criterion from Query's Select records panel, because the join will select the records for you. At runtime, permit the operator to enter the date through some mechanism of your choice, such as a display file and RPG program. Clear the work file, and then write whatever date the operator enters into the work file.

Here's an example that may help. Suppose the records are in a production file called PURCHORD, which contains data about purchase orders. The date field used to select records is called DUEDATE. The work file is called DATESLT, and the field is called SDATE.

Under the Specify file selections option, enter the two file names, DATESLT and PURCHORD. Since it's an inner join, it really doesn't matter which one you specify first. Press Enter to advance to the Specify type of join panel, and choose option 1 (Matched records) for an inner join. Press Enter to advance to the Specify how to join files panel and enter the following:

SDATE EQ DUEDATE

Press Enter to return to the Define the query panel. When you run the query, you will get only the records with a due date matching the date in the work file.

If you want to select on a group of values, such as two or more dates (but not a range), write more records to the work file at runtime. This is similar in function to the LIST operator. If you want to select on a range of values, add a second field of the same type and size to the work file record format. In this example, you might call them SFROMDATE and STHRUDATE. Use two join criteria, like this:

SFROMDATE LE DUEDATE
STHRUDATE GE DUEDATE

If you are selecting by more than one field, and the conditions are ANDed, you can add the second field to the work file and reference that field in the join criteria as well. For example, if you were to select both by date and by buyer ID, your join would look like this.

SDATE EQ DUEDATE
SBUYER EQ BUYERID

SDATE and SBUYER are the fields in the work file. DUEDATE and BUYERID are in the PURCHORD file.

This method of selecting records has another advantage. It lets you run queries in batch mode. Figure 1 contains a program called RUNJOIN1. When you call it (from a menu or the command line), it runs interactively to prompt for the record selection criteria. Then it submits itself to batch. In batch mode, it runs the query, here called JOIN1.

/* Program RUNJOIN1 */
PGM

DCL VAR(&JOBTYPE) TYPE(*CHAR) LEN(1)

RTVJOBA TYPE(&JOBTYPE)
IF COND(&JOBTYPE *EQ '0') THEN(GOTO +
              CMDLBL(BATCHRUN))

/* this part runs interactively */

/* load file with your favorite mechanism */
STRDFU OPTION(5) FILE(&USER/DATESLT)
/* submit to batch to run the query */
SBMJOB CMD(CALL PGM(RUNJOIN1))
RETURN

/* this part runs in batch */

BATCHRUN:
RUNQRY QRY(JOIN1)
ENDPGM

Figure 1: Program RUNJOIN1 enables you to run queries in batch mode.

One last comment: This illustration uses only two files, but the query can include more files. In the case of an inner join, the result set should be identical to the one you got when you had the record selection criteria in the Select records panel. However, if the join is an outer join and a record in the work file does not match any records in the secondary file to which it is joined, the result set will contain a record with the unmatched value. All other fields in that record will be null.

Also Read

The Power of Writing Code in a Low-Code Solution

TechTip: Let’s Have a Date, Part 2

Going off the Rails