IBMi (AS400) fans only ' How to pass parameters to a Query

#IBMiSample
Each IBMi user knows they run the most efficient system. All others just have too much money.

First of all look at this post and compile and populate OTORD00F file to use this example.

Let's create a query called Q01A

:ccm represents our parameter to pass to the query.

Type F3

Now let's write the CL to pass the ccm parameter to the query:

Q01ACL CL

****************** Inizio dati *******************************

             PGM        PARM(&CCM)                            

             DCL        VAR(&CCM) TYPE(*CHAR) LEN(5)          

             STRQMQRY   QMQRY(Q01A) OUTPUT(*) QMFORM(*QMQRY) +

                          ALWQRYDFN(*YES) SETVAR((&CCM &CCM))  

            ENDPGM                                            

******************** Fine dati *******************************

We save and compile the CL.

Now let's try.

Type:

CALL PGM(Q01ACL) PARM(('103'))

I have two records into OTORD00F with OTCCM0 = '103'.



This is the result:

Query . . . . .:   VDOTEST1/Q01A             Larghezza .:       123

Modulo  . . . .:   VDOTEST1/Q01A             Colonna . .:         1

Controllo  . . .

Riga  I....+....1....+....2....+....3....+....4....+....5....+....6....+.

 OTANN0      OTCOR0  OTTIP0      OTCCL0    OTDTA0    OTDTP0  OTCCM0

 ------  ----------  ------  ----------  --------  --------  ------

                 2   10              2   190,108   190,112   103   

                 6   10              2   190,108   190,112   103   

******  * * * * *  F I N E  D E I  D A T I  * * * * *                    

That's it

Update to post

One of my very few readers asked me:

:CCM is a numeric parameter; I receive an error or empty result with a character parameter. Please could you reply how to configure the query to receive numeric and the *char parameters ?


Well, If you define a new numeric Type variable &CCL like in the example below (1), and if you add &CCL as a parameter into STRQMQRY (2)

the compiler gives an error
* CPD0776 30  Variable &CCL for parameter SETVAR must be *CHAR.

Actually, STRQMQRY only accepts alphanumeric parameters.


To answer my kind reader's question:
Define an alphanumeric field also to make a selection on an alphanumeric field, as in the example below:

add to the QUERY the new selection on OTCCL0 (OTCCL0 is numeric 7,0):

then type:
CALL PGM(Q01ACL) PARM(('102') ('4'))

I have two records into OTORD00F with OTCCM0 = '102' and OTCCL0 = 0000004

There is another technique which does not pass parameters to the QUERY. It involves writing the parameters to a file and adding this file to the QUERY.

Let's see how:

Suppose I want to filter OTORD00F for a numeric field, OTDTA0 (6 0).

First of all I create the file which will contain the parameter, so I type STRSQL and then

CREATE TABLE Q02APARM (

  Q2DTST numeric(6, 0)  )
RCDFMT Q02AP

Then I write into Q02APARM

INSERT INTO Q02APARM (Q2DTST) VALUES (190108)

Now I create the QUERY (the query name is Q02A)

Type 1 like above

Fill like above

Choose 1 like above

Fill like above

Type F3 and save the Query

Fill like above

Create and compile the CL procedure Q02ACL (CLP):

            PGM                                              

            STRQMQRY   QMQRY(Q02A) OUTPUT(*) QMFORM(*QMQRY) +

                          ALWQRYDFN(*YES)                     

            ENDPGM                                            

Type CALL Q02ACL

In my OTORD00F there are 6 Records with OTDTA0=190108

If anyone has a better solution, the input would be appreciated.

See https://www.ibm.com/docs/en/i/7.4?topic=ssw_ibm_i_74/cl/strqmqry.html for more details.

I appreciate all the comments made on this blog.