Reader Feedback and Insights

Kudos for the Guru

I love the site. It's nice to have access to people in the industry that know what they are doing and are willing to share their knowledge. If you guys could just update the Four Hundred Guru page every day, life would just about be perfect!!!

--Bill

"Creating Dynamic Queries," by Bruce Guetzkow

Great article on parameters in STRQMQRY. However, the DCL statements might not be clear in the article.

The DCL statement for an 8 position alpha field would be as follows:

DCL   VAR(&ALPHA)   TYPE(*CHAR)   LEN(10)   VALUE('''abcdefgh''')

The DCL statement for an 8 position numeric field would be:

DCL   VAR(&NUMERIC)   TYPE(*CHAR)   LEN(8)   VALUE('12345678')

The alpha CHGVAR statement would be:

CHGVAR   VAR(%SST(&ALPHA 2 8))   VALUE(ALPHA_PARM)   

The numeric CHGVAR statement would be:

CHGVAR   VAR(&NUMERIC)   VALUE(NUMERIC_PARM)

The number of single quotation marks (') at the beginning and at the end of the alpha field must be 3, and the "numeric" field must be 1.

Thanks.

--Richard

Bruce Guetzkow responds: I typically use the following when I need to add quotes inside a variable:

DCL  VAR("E)    TYPE(*CHAR)  LEN(1)  VALUE('''')
DCL  VAR(&ALPHA10)  TYPE(*CHAR)  LEN(10)
DCL  VAR(&ALPHA12)  TYPE(*CHAR)  LEN(12)

CHGVAR  VAR(&ALPHA12)  VALUE("E *CAT &ALHPA10 *CAT "E)

This eliminates trying to figure out how many quotation marks I need in every concatenation. I only have to define the quotation mark one time, then use it wherever I need it. It makes the code much easier to read.

I've been using QMQRY for about 10 years now, and the biggest problem I've had is figuring out when I needed quotes and how many levels of quotes might be needed. (What if I'm looking for the quotation character as part of the text in my file?) It's at this point that I start looking for a consultant!

Thanks for the comment.

--Bruce Guetzkow
bguetzkow@itjungle.com

Very interesting article. I enclose the following post from Birgitta Hauser that I picked off of a newsgroup last year that allows prompting good old Query/400 queries without having to create the QMQRY. It was a real eye opener for me! Keep up the good work. Four Hundred Guru is one of the few newsletters I read.

--Pete Hurd

Ideas on full variables in Queries/CL: Do you want to pass variables for selection to a Query/400 and then run it? If so, change your selections in your Query using variables.

   :VarName
   Before:
   AND/OR  Feld              Test   Wert (Feld, Nummer, 'Zeichen'
oder...)
           FIRNR             EQ     100
    AND    ADTPA             EQ     'E'

   After:
   AND/OR  Feld              Test   Wert (Feld, Nummer, 'Zeichen'
oder...)
           FIRNR             EQ     :PFINR
    AND    ADTPA             EQ     :PTPA

Write a CL program to fill your variables, and run your query using STRQMQRY, instead of RUNQRY.

 PGM        PARM(&P$FINR &P$TPA)             

DCL        VAR(&P$FINR)  TYPE(*DEC)  LEN(3) 
DCL        VAR(&QryFINR) TYPE(*CHAR) LEN(3) 

DCL        VAR(&P$TPA)   TYPE(*CHAR) LEN(2) 
DCL        VAR(&QryTPA)  TYPE(*CHAR) LEN(4) 

ChgVar     Var(&QryFiNr) Value(&P$FINR)     
ChgVar     Var(&QryTpa)                     
                  Value('''' *cat &P$TPA *Cat '''')

StrQmQry   QmQry(MYQUERY)                  +
           Output(*PRINT)                  +
           QmForm(*QMQRY)                  +
           AlwQryDfn(*YES)                 +
            SetVar((PFINR &QryFiNr) (PTPA &QryTpa))

          ENDPGM