Passing parms to a Query

Query/400 has always provides a quick and easy way to generate a report or create an output file. I have found that once they are built they never go away, as a programmer never has time, with all the demands on their time, to replace it with a RPG program.

If a Query joins several large files it can become a resource hog, taking away CPU power from normal daily interactive processing. The solution has always been to run Queries in batch.

Most people find that if they need to change the Query’s run parameters they use the Record Select parameter, RCDSLT(*YES), in the Run Query command, RUNQRY.

RUNQRY QRY(QUERY_NAME) RCDSLT(*YES)

Of course this cannot be run in batch. Therefore, this Query, with its large joins, runs interactively, and the other users complain that the system is running slow.

But there is another way using the Start Query Management Query, STRQMQRY, command.

In this example we have a Query, TEST, which lists all of the orders of a certain status within a date range. The Status field is a one character field called STS. The Order Date is an eight long packed numeric field, called ORDDTE.

The current selection criteria in the Query are:

Select Records Type comparisons, press Enter. Specify OR to start each new group. Tests: EQ, NE, LE, GE, LT, GT, RANGE, LIST, LIKE, IS, ISNOT... AND/OR Field Test Value (Field, Number, 'Characters' STS EQ 'S' ORDDTE RANGE 20130101 20131231

Why does he do that?   There is no AND in the second line of selection criteria as Query assumes that if there is no value it is AND.

First, we need to change the values in the selection criteria to be variables:

Select Records Type comparisons, press Enter. Specify OR to start each new group. Tests: EQ, NE, LE, GE, LT, GT, RANGE, LIST, LIKE, IS, ISNOT... AND/OR Field Test Value (Field, Number, 'Characters' STS EQ :STS ORDDTE RANGE :FRMDTE :TODTE

The Status of ‘S’ has been replaced by the variable :STS, yes it must start with a colon, and the Order Date range by variables :FRMDTE and :TODTE.

When Enter is pressed we are presented with the ‘Specify Dependent Value Qualifiers’ screen:

Specify Dependent Value Qualifiers Type choices, press Enter. Qualifier type . . . . 1 1=Query, 2=File Query or file . . . . Name, F4 for list of files Library . . . . . . QGPL Name, *LIBL, F4 for list For choice 2=File: File member . . . . Name, *FIRST, F4 for list

I have to admit I do not know what the functionality of the screen is. All I know to do it to enter something in the ‘Query or file’ field, for example: TEST, and press Enter.

You cannot press F5 to run the Query when editing it as you will receive the following message: ‘Dependent value used, cannot show report.’

Now we need to do some CL programming. I am not going to give the code on how to create a screen that allows the requester to enter the Status and From and To dates. All I am going to give is the program that is submitted to batch.

The program receives three parameters, see line 1 below, from the program that submitted it to batch:

• &STS – Status code – 1 character.
• &FRM – From date – 8 characters.
• &TO – To date – 8 character.

01 PGM PARM(&STS &FRM &TO) 02 03 DCL VAR(&STS) TYPE(*CHAR) LEN(1) 04 DCL VAR(&FRM) TYPE(*CHAR) LEN(8) 05 DCL VAR(&TO) TYPE(*CHAR) LEN(8) 06 07 DCL VAR(&STS2) TYPE(*CHAR) LEN(10) 08 09 CHGVAR VAR(&STS2) VALUE('''' || &STS || '''') 10 11 STRQMQRY QMQRY(TEST) OUTPUT(*PRINT) QMFORM(*QMQRY) + 12 ALWQRYDFN(*YES) + 13 SETVAR((STS &STS2) + 14 (FRMDTE &FRM) + 15 (TODTE &TO)) 16 17 ENDPGM

Line 7 has the field &STS2 defined as 10 characters. This field has to be large enough to contain the value of &STS and the apostrophes needed to make it appear character to the Query.

Line 9 adds the apostrophes either side of &STS, so that it will appear as ‘x’ to the Query.
Why does he do that?   The double pipe, ||, does the same as the *CAT expression. I just think it is easier to enter and more pleasing on the eye.

Line 11 is the start of the STRQMQRY command.

Line 12 has the ALWQRYDFN(*YES) parameter which tells the command to use the Query TEST.

Line 13, 14, and 15 is where we tell the Query what values to use. The variable name we defined in the Query appears first followed by the CL variable name.

Field &STS2 has leading and trailing apostrophes to denote that it is a character/alphanumeric field, which matches the definition of the file field STS.

Fields &FRM and &TO have no apostrophes, therefore, the Query assumes that they are decimal/numeric, which matches the definition of the file field ORDDTE.

If we wanted to produce an output file, in this example a file called TESTFILE in the library QTEMP, we would change the STRQMQRY command. The OUTPUT parameter would be *OUTFILE and we would need to give the OUTFILE parameter:

STRQMQRY QMQRY(TEST) OUTPUT(*OUTFILE) QMFORM(*QMQRY) + OUTFILE(QTEMP/TESTFILE) + ALWQRYDFN(*YES) + SETVAR((STS &STS2) + (FRMDTE &FRM) + (TODTE &TO))

Now you have a simple way of always running Queries in batch, even those that need parameters to determine what data to extract.

 

This article was written for IBM i 7.1, and it should work with earlier releases too.