---

Return to the CL Tips

---

FOOLPROOF TECHNIQUES FOR OPNQRYF COMMAND SELECTION STRING

Frustrated by problems when creating a valid QrySlt (Query Selection) parameter string for the OpnQryF (Open Query File) command? Here are several techniques that can make this programming task a lot simpler and more reliable.

To illustrate the techniques, I use the following CL program. (Error handling has been omitted to conserve space.)

/* Program QryExm */
Pgm Parm( &City     +
          &Discount )

Dcl &City       *Char (   32   )
Dcl &Discount   *Dec  (   15 5 )

Dcl &DiscStr    *Char (   20   )
Dcl &QrySltStr  *Char ( 5000   )
Dcl &Quote      *Char (    1   ) Value( '"' )

/* Convert the numeric value to a character representation. */

ChgVar &DiscStr &Discount

/* Build the query selection string */

ChgVar &QrySltStr					                 +
     ( 'City = '     *Cat &Quote *Cat &City *TCat &Quote +
       *Cat ' *And ' *Cat                                +
       'Discount > ' *Cat &DiscStr                       )

OvrDbF  File( Customer ) Share( *Yes )
OpnQryF File( Customer ) QrySlt( &QrySltStr )
Call    DspCust
Clof    OpnId( Customer )
DltOvr  File( Customer )

Return
EndPgm

This program takes two arguments: a city name and a discount amount. You can call this program from Command Entry, as in the following example:

Call QryExm ( 'Portland' 2.5 )

This call would produce a &QrySltStr value of

City = "Portland" *And Discount > 00000000000002.50000

Here are the tips:

a. Always construct the OpnQryF command's QrySlt parameter as a string stored in a character variable (e.g., &QrySltStr). This simplifies coding and -- most importantly -- lets you inspect the selection string using the interactive debugger. The query selection string can be up to 5,000 characters long.

b. Declare a mnemonic (e.g., &Quote) to use instead of literals in string expressions. This makes your code easier to read and helps avoid typos. (You may also want to declare mnemonics for the *And and *Or operators, although I haven't done that in this example.)

c. If the selection string has multiple conditions connected by *And or *Or operators, put each Boolean operator on its own line and start each condition on a new line. This improves the readability of your code and makes it easier to check for balanced quotes around character values.

d. Use only character CL variables to hold comparison values. You cannot use the string concatenation operators (e.g., *Cat) with a decimal variable.

e. If you need to compare a field to a number (as is done with the Discount field in this example), the number must still be represented as a character string. You can use a ChgVar command (as shown) to convert a decimal CL variable to a character variable. Be sure you declare the character variable large enough to hold all significant digits, as well as the decimal point and a potential negative sign. Never add quotes around a value that's compared to a numeric field.

f. Always concatenate a pair of quotes around values that are compared to a character field (e.g., City). You can use the *TCat operator, as shown, to trim trailing blanks from the comparison value.

If you follow these basic rules, you should find complex QrySlt expressions are a lot easier to work with.

[report a broken link by clicking here]

---