Pass Parameters to a Query/400 Query Using a Derived QM Query

Many users are comfortable with writing Query/400 queries and aren't interested in learning a great deal about Query Management (QM) queries. However, they still have a need to run queries that use parametric information. Here's a way to let the system derive a QM query from the Query/400 query, complete with parameters.

Let's begin by modifying our tried and true Query/400 query whose Select Records panel appears below.

                                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', or ...) 
        LOCATION          EQ     'HOUSTON'                                   
 AND    DEPARTMENT        EQ     'SALES'

Let's instruct the query to select records based on parameter values for Location and Department. Edit the query (presume EmpQry for our example) and select the Select Records panel. Once there, enter the following selection criteria:


                                 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', or ...)  
         LOCATION          EQ     :INLOC                                       
  AND    DEPARTMENT        EQ     :INDEPT

Here, we've defined fields :InLoc and :InDept (the colon prefix defines these as fields) to use in our record selection. When you exit this panel, you'll see the Specify Dependent Value Qualifiers panel below.

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

This panel's intent is to determine and define to the query the location where information for :InLoc and InDept originates. Dependent queries obtain some of the information required for them to execute from either another query's output or the contents of a file. This was used mostly by OfficeVision for merging information into documents. Our technique doesn't use any of this information, so I won't bore you with the details! Simply provide some dummy information to satisfy the edit session and save the query definition.

You can't run a dependent query directly from the WrkQry panel or using command RunQry. However, you can run it using the following command:

  StrQMQry  QMQry(YourLib/EmpQry) AlwQryDfn(*Yes)

Notice parameter AlwQryDfn. A value of *Yes for this parameter instructs the system to derive a temporary QM query by retrieving runtime information from the Query/400 query.

Once the derived QM query begins, you'll see the Display Program Messages panel asking you to enter a value for field INLOC.


********************************************************************************
*                           Display Program Messages                           *
*                                                                              *
* Job 361742/GGUTHRIE/SNBGARYA1 started on 08/14/02 at 07:40:11 in subsystem Q *
* Type a value for variable "INLOC" and press Enter.                           *
*                                                                              *
*                                                                              *
*                                                                              *
*                                                                              *
*                                                                              *
*                                                                              *
*                                                                              *
*                                                                              *
*                                                                              *
*                                                                              *
*                                                                              *
*                                                                              *
*                                                                              *
* Type reply, press Enter.                                                     *
*   Reply . . .   'HOUSTON'                                                    *
********************************************************************************

Because this is a character field, you should enclose the value in apostrophes (e.g., 'HOUSTON').

After you enter a value for INLOC, the system will prompt you to enter a value for INDEPT (e.g., 'SALES'), after which you will see the results of the query (e.g., all employees working in the SALES department at the Houston location).

You can also specify the values for the parameters directly on the StrQMQry command, in which case the system does not prompt you for the values. The following snapshot of the StrQMQry command demonstrates how to enter the parametric values in the Set variables (SetVar) parameter.

                     Start Query Management Query (STRQMQRY)

 Type choices, press Enter.

 Query management query . . . . . > EMPQRY        Name
   Library  . . . . . . . . . . . >   YOURLIB     Name, *LIBL, *CURLIB
 Output . . . . . . . . . . . . .   *             *, *PRINT, *OUTFILE
 Query management report form . .   *SYSDFT       Name, *SYSDFT, *QMQRY
   Library  . . . . . . . . . . .                 Name, *LIBL, *CURLIB

                            Additional Parameters 

 Relational database  . . . . . .   *NONE 
 Connection Method  . . . . . . .   *DUW          *DUW, *RUW 
 User . . . . . . . . . . . . . .   *CURRENT      Name, *CURRENT
 Password . . . . . . . . . . . .                 Character value, *NONE
 Naming convention  . . . . . . .   *SYS          *SYS, *SQL, *SAA 
 Allow information from QRYDFN  .   *YES          *NO, *YES, *ONLY
 Set variables: 
   Variable name  . . . . . . . . > INLOC 
   Variable value . . . . . . . . > '''HOUSTON'''
   Variable name  . . . . . . . . > INDEPT 
   Variable value . . . . . . . . > '''SALES'''

This technique gives your CL programs the ability to execute SQL statements and at the same time take advantage of the use of parameters. Remember, your CL program must enclose character parameter values in apostrophes as the following sample program demonstrates.

/*  ====================================================================  */
/*  = Sample CL program using StrQMQry command                         =  */
/*  ====================================================================  */

Pgm
( &Location                                                                +
  &Department                                                              +
)                                                          

  Dcl        &Location   *Char    (   10   )
  Dcl        &Department *Char    (   10   )
  Dcl        &InLoc      *Char    (   12   )
  Dcl        &InDept     *Char    (   12   )
  ChgVar     &InLoc      ( '''' *Cat &Location *Cat '''' )
  ChgVar     &InDept     ( '''' *Cat &Department *Cat '''' )

  StrQMQry   QMQry( YourLib/Employees2)                                    +
             SetVar( ( INLOC &INLOC ) ( INDEPT &INDEPT) ) 

EndPgm