One of my programmers came into my office. By his huffing, puffing, and red face, I knew something was wrong. You see, I am an information services manager.

"Boss," he gasped, "do you remember the inventory transaction analysis report program I wrote for Sally? She's happy. Says it's just what she needs. It's really flexible. She can run it over ranges of item numbers, ranges of transaction dates, and ranges of transaction types." He paused for dramatic effect. "However, everybody else is complaining that response time goes to pot when this report runs."

"Did you use OPNQRYF?" I asked.

"Yes," he replied.

I took a quick look at the CL code. The problem was as obvious as a red nose on a clown's face. His performance was rotten because of the way he had written his OPNQRYF statement. The QRYSLT parameter had a long string of unnecessary record selection criteria. The system was wasting time, making record selection decisions about database fields that didn't even matter.

"Do you know how to use a CL variable in the query select parameter?" I asked, knowing full well the answer.

"No, not really."

This guy's CL program needed help, and I had a trick up my sleeve that would help him.

OPNQRYF is one of the more powerful tools found on any AS/400. It can preselect records in almost any sequence and filter out most records you want filtered out. However, with this power comes some complexity. Setting up the Query select (QRYSLT) parameter can be difficult, especially when trying to set up a dynamic query select variable.

Let's take the example of running a report against an item transaction file. You may want to set

up a display screen, accompanying CL, and a program to list certain transactions. Let's suppose that you want to make a range of six fields, to and from item number, to and from date, and to and from transaction. We will define the fields as in Figure 1.

This article will focus on building a dynamic query select variable rather than display or report specifications. What does the OPNQRYF command want to see in a query select variable? First of all, it must be a character, and up to 512 characters long. We will declare it as this:

DCL +

VAR(&QRYSLT) +
TYPE(*CHAR) +
LEN(512)

To see how to format that variable, let's work backward and see what OPNQRYF wants to see. Figure 2 shows a CL statement, with arbitrary variables, formatted the way OPNQRYF would want to see it.

Note the double quotes around the values being compared to character variables ITEMNO and TRNTYPE and the absence of quotes around the values being compared to the numeric variable TRNDATE. This select uses the %RANGE function to tell OPNQRYF to select records within this range of values. First, let's substitute two CL variables (&FROMDATE *CHAR 8 and &TODATE *CHAR 8) in the place of the date ranges. These variables get their values from a declared display file. See Figure 3.

Note the changes after the TRNDTE range. There are two new *CATs and a *BCAT. In short, this places the date variables in the OPNQRYF expression with a blank in the middle. The *CATs in CL mean concatenate, and the *BCAT means leave one blank and concatenate. This means take the date ranges and concatenate them with a blank between them. Also, in both of the query select variables, the entire query select variable is enclosed by single quotes (remember that it is a 512-byte character variable). In the date ranges, note the quote around *CAT &FROMDATE *BCAT &TODATE *CAT. This tells OPNQRYF to suspend the static data and replace the variable data.

Let's take another step. From the variable called &QRYSLT, which is defined above, we can take the entire QRYSLT string and substitute it in the query select variable. You can see the result in Figure 4.

Now, we have a variable with the query select statement in it. To call the OPNQRYF statement, just substitute the value of &QRYSLT into it.

OPNQRYF +

FILE((ITEMTRAN)) +
QRYSLT(&QRYSLT)

Instead of substituting a numeric variable into the select variable, let's substitute a character variable. In the %RANGE for the item number variable (ITEMNO), the values are delimited by double quotes. This is because item numbers are character fields in the database file. This must also be done when we use a query select variable.

Assume that we have two variables passed to us from a display file declared in the CL program, &FROMITEM and &TOITEM. See Figure 5.

We had to leave the first double quote, followed immediately by a single quote and then *CAT. This tells OPNQRYF that the ITEMNO variable has a character range. Then, we simply enclose

each variable name with *CATs, with a space in the middle ('" "'). Or we can say that we concatenate a double quote, a from field, two double quotes separated by a space, a to field, and a final double quote.

The transaction type range is similar. See Figure 6.

This will make a completed variable to pass to the QRYSLT parameter.

Why, you may ask, would we want to do that? Wouldn't it be just as easy to put the same stuff in the QRYSLT parameter? That's true, but I have found in my programming trials and tribulations that, with display files, I often preload character variables with blanks and nines to include everything and let the users change them to the ranges they want.

For example, suppose a user is looking for transactions for an item in a date range and he doesn't care what the transactions are. You could pass the same statement, as above. However, you could also pass only the item and date ranges and leave the transaction types out of the equation. When the query has fewer ranges to contend with, it can run faster. The optimizer may find (depending upon your system) an already existing logical path to use for just item and date. You can use a conditional IF statement to load only those variables you need. You can set the date ranges as a constant. The reasoning for this is that, over 99 percent of the time, some kind of date range is used. Very seldom does a user use default date ranges, except by mistake. See Figure 7.

Coding the next portion, if there is an item range, add that range to the query select variable. For liveCL,seeFigure8.

Likewise, with the transaction type (Figure 9).

Finally, we have a dynamic query select variable that should save some processing cycles in OPNQRYF.

The reasoning is this: Suppose you have three file folders, with 200 pages in each file folder. You are looking for only a few specific pages. If you know that you need the entire second folder, it takes you less time to pull that folder than if you had to go through each of the 600 pages. The same should hold true with fields in a database record. If you already know that you need the entire file in regard to that field, why pass that field OPNQRYF? There is no need to.

Most AS/400s can use a programming performance boost. OPNQRYF is known as a performance "gas guzzler." This way, with some programming technique, you can "tune" the OPNQRYF "engine" without adding additional memory or purchasing a new processor.

"First," I said, "do you now understand how to correctly format a query select variable?"

"Yes," my programmer replied. "These examples make sense to me. I understand all the quote problems I had writing this for the first time."

"Now," I said, "take these examples, go back, and rewrite the CL. Then, get some dedicated system time and run both programs to see which runs faster. Let me know how it turns out."

The next day, that programmer came back to my office. His attitude was much different this time.

"This dynamic query select is good stuff, boss. I think the users won't complain as much now. Plus, I now understand better how OPNQRYF works."

Another day, another good deed. The programmer learned something, and, like a good manager, I let him take the credit.

Tim Johnston is the manager of Information Systems at Hapco, a Division of Kearney National, Inc. Hapco is a manufacturer of aluminum light and flag poles. Tim can be found lurking around the MC discussion forum, or you can reach him by email at This email address is being protected from spambots. You need JavaScript enabled to view it..

Figure 1: These are the variables used in this example.

Item Number Character 15

Date Decimal 8,0

Transaction Type Character 2 OPNQRYF FILE((ITEMTRAN)) +

QRYSLT('ITEMNO *EQ %RANGE("A123" "B345") *AND +

TRNDATE *EQ %RANGE(19970101 19970630) *AND +
TRNTYPE *EQ %RANGE("A" "B")')

OPNQRYF FILE((ITEMTRAN)) +

QRYSLT('ITEMNO *EQ %RANGE("A123" "B345") *AND +

TRNDATE *EQ %RANGE(' *CAT +
&FROMDATE *BCAT +
&TODATE *CAT +
') *AND TRNTYPE *EQ %RANGE("A" "B")')

CHGVAR VAR(&QRYSLT) +

VALUE('ITEMNO *EQ %RANGE("A123" "B345") *AND +

TRNDATE *EQ %RANGE(' *CAT +
&FROMDATE *BCAT +
&TODATE *CAT +
') *AND TRNTYPE *EQ %RANGE("A" "B")')

CHGVAR VAR(&QRYSLT) +

VALUE('ITEMNO *EQ %RANGE("' *CAT +

&FROMITEM *CAT +
'" "' *CAT +
&TOITEM *CAT +
'") *AND TRNDATE *EQ %RANGE(' *CAT +
&FROMDATE *BCAT +
&FROMDATE *CAT +
') *AND TRNTYPE *EQ %RANGE("A" "B")')

OPNQRYF FILE((ITEMTRAN)) QRYSLT(&QRYSLT)

CHGVAR VAR(&QRYSLT) +

VALUE('ITEMNO *EQ %RANGE("' *CAT +

&FROMITEM *CAT +
'" "' *CAT +
&TOITEM *CAT +
'") *AND TRNDATE *EQ %RANGE(' *CAT +

Figure 2: How OPNQRYF would want to see the code

Figure 3: Using variables for beginning and ending dates

Figure 4: Using a variable for the QRYSLT parameter

Figure 5: Same, for beginning and ending item numbers

Figure 6: Same, for beginning and ending transaction types

&FROMDATE *BCAT +
&FROMDATE *CAT +
') *AND TRNTYPE *EQ %RANGE("' *CAT +
&FROMTRN *CAT +
'" "' *CAT +
&TOTRN *CAT '")')

OPNQRYF FILE((ITEMTRAN)) QRYSLT(&QRYSLT)

CHGVAR VAR(&QRYSLT) +

VALUE('TRNDATE *EQ %RANGE(' *CAT +

&FROMDATE *BCAT +
&TODATE *CAT ')')

IF COND(&FROMITEM*NE''*AND&TOITEM*NE'999999999999999')+

THEN(DO)
CHGVAR VAR(&QRYSLT) +

VALUE(&QRYSLT *BCAT +

'*AND FMITM *EQ %RANGE("' *CAT +
&FROMITEM *CAT +
'" "' *CAT +
&TOITEM *CAT +
'")')

ENDDO IF COND(&FROMTRN*NE''*AND&TOTRN*NE'99')THEN(DO)

CHGVAR VAR(&QRYSLT) +

VALUE(&QRYSLT *BCAT +

'*AND FMTRN *EQ %RANGE("' *CAT +
&FROMTRN *CAT +
'" "' *CAT +
&TOTRN *CAT +
'")')

ENDDO

Figure 7: Starting to build QRYSLT expression

Figure 8: Adding to QRYSLT expression

Figure 9: Adding more to QRYSLT expression

Also Read

9 data governance strategies that will unlock the potential of your business data

Data observability: The missing piece in your data integration puzzle

From Legacy to Modern: Upgrading Your BI Strategy with LANSA BI for IBM i