Ü  QMQRY

·         QMQRY stands for Query Management Query.

·         We create an object of type *QMQRY which contains SQL statement that is dynamic and can be changed at runtime. Hence, we use this whenever we need dynamic SQL statement.

·         The source member that holds the dynamic SQL statement is of source type QMQRY.

·         There can be only one SQL statement in a QMQRY object.

 

Examples

 

I.            QMQRY to be called by CL program

 

ü  First of all create a source member of type QMQRY as below.

 


                               Work with Members Using PDM                 UKFAI005
 
File  . . . . . .   QRPGLESRC
Library . . . .     AMIT                 Position to  . . . . .
 
Type options, press Enter.
2=Edit         3=Copy  4=Delete 5=Display       6=Print     7=Rename
8=Display description  9=Save  13=Change text  14=Compile  15=Create module...
 
Opt  Member      Type        Text
QUERYC_C5   QMQRY       QMQUERY TO BE CALLED BY QUERYCL_C5    >>>>>>>>>>>>>>>>>>>>>>
 
  
 
Parameters or command
===>
F3=Exit          F4=Prompt             F5=Refresh            F6=Create
F9=Retrieve      F10=Command entry     F23=More options      F24=More keys
    

 

 

ü  The source code for the above source member of source type QMQRY is as shown below:

 

 


Columns . . . :    1  80                     Browse                      AMIT/QRPGLESRC
SEU==>                                                                        QUERYC_C5
FMT **  ...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8
*************** Beginning of data *****************************************************
0001.00   &SEL  &FLD  &FR                                                                   
0002.00   &FILE                                                                             
0003.00   &WH                                                                                
0004.00   &COMP &VAL                                                                        
****************** End of data *********************************************************


 

ü  CRTQMQRY QMQRY(AMIT/QUERYC_C5) SRCFILE(AMIT/QRPGLESRC) SRCMBR(QUERYC_C5) 


Columns . . . :    1  80                      Edit                         AMIT/QRPGLESRC
SEU==>                                                                        QUERYCL_C5
FMT **  ...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8
*************** Beginning of data ***********************************************
0001.00 PGM
0002.00              DCL        VAR(&SEL_Q) TYPE(*CHAR) LEN(8) VALUE(SELECT)
0003.00              DCL        VAR(&FLD_Q) TYPE(*CHAR) LEN(30) +
0004.00                         VALUE('ORG, ACC, CCY, PARTY, ACCSTS')
0005.00              DCL        VAR(&FR_Q) TYPE(*CHAR) LEN(10) VALUE('FROM')
0007.00              DCL        VAR(&FILE_Q) TYPE(*CHAR) LEN(10) +
0008.00                           VALUE('ACCOUNT')
0009.00              DCL        VAR(&WH_Q) TYPE(*CHAR) LEN(7) VALUE('WHERE')
0010.00              DCL        VAR(&COMP_Q) TYPE(*CHAR) LEN(6) VALUE('PARTY=')
0011.00              DCL        VAR(&VAL_Q) TYPE(*CHAR) LEN(15) +
0012.00                           VALUE('''P00000000002''')
0013.00              MONMSG MSGID(CPF0000) EXEC(GOTO  ERROR)
0014.00 /*_______________________________________________________________*/
0015.00
0016.00              STRQMQRY   QMQRY(AMIT/QUERYC_C5) SETVAR((SEL &SEL_Q) +
0017.00                           (FLD &FLD_Q) (FR &FR_Q)              +
0018.00                           (FILE &FILE_Q) (WH &WH_Q) (COMP &COMP_Q) +
0019.00                           (VAL &VAL_Q))
0020.00              GOTO       CMDLBL(END)
0005.00              DCL        VAR(&FR_Q) TYPE(*CHAR) LEN(10) VALUE('FROM')
0007.00              DCL        VAR(&FILE_Q) TYPE(*CHAR) LEN(10) +
0008.00                           VALUE('ACCOUNT')
0009.00              DCL        VAR(&WH_Q) TYPE(*CHAR) LEN(7) VALUE('WHERE')
0010.00              DCL        VAR(&COMP_Q) TYPE(*CHAR) LEN(6) VALUE('PARTY=')
0011.00              DCL        VAR(&VAL_Q) TYPE(*CHAR) LEN(15) +
0012.00                           VALUE('''P00000000002''')
0013.00              MONMSG MSGID(CPF0000) EXEC(GOTO  ERROR)
0014.00 /*_______________________________________________________________*/
0015.00
0016.00              STRQMQRY   QMQRY(AMIT/QUERYC_C5) SETVAR((SEL &SEL_Q) +
0017.00                           (FLD &FLD_Q) (FR &FR_Q)              +
0018.00                           (FILE &FILE_Q) (WH &WH_Q) (COMP &COMP_Q) +
0019.00                           (VAL &VAL_Q))
0020.00              GOTO       CMDLBL(END)
0021.00 ERROR:
0022.00              SNDUSRMSG  MSG('SOME ERROR IN CALLING THE QMQUERY')
0023.00 END:
0024.00 ENDPGM
****************** End of data ****************************************************

 

 

ü  Compile and run the program to see the output below:

 

OUTPUT

 


                           Display Report
 
Query . . . . .:   AMIT/QUERYC_C5            Width . . .:       123
Form  . . . . .:   *SYSDFT                   Column  . .:         1
Control  . . . .
Line   ....+....1....+....2....+....3....+....4....+....5....+....6
ORG CODE  ACC NUMBER    CURRENCY  PARTY NUM     A/C STS
--------  ------------  --------  ------------  -------
000001       190   100000000002  EUR       P00000000002       2
000002       190   100000000003  USD       P00000000002       2
******  * * * * *  E N D  O F  D A T A  * * * * *


 

 

 

 

 

II.            To run QMQRY from command line

 

The above created query QUERYC_C5 can also be run from the command line with the command as below:

 


STRQMQRY QUERYC_C5   
 
PRESS ENTER;

 

 

After we press Enter, it will ask for the value of each variable that is needed to run the SQL statement.

Enter the value for each variable that is there in the QMQRY source code as shown below:

 

 


Display Program Messages
 
Job 690848/AJAISWAL/SB started on 13/02/13 at 06:09:30 in subsystem QINTER i
 
Type a value for variable "SEL" and press Enter.
SELECT                                            >>>>>>>>>>>> SEL = SELECT
Type a value for variable "FLD" and press Enter.
ORG, ACC, CCY, PARTY, ACCSTS                      >>>>>>>>>>>> FLD = ORG, ACC, CCY, ACCSTS
Type a value for variable "FR" and press Enter.
FROM                                              >>>>>>>>>>>> FR = FROM
Type a value for variable "FILE" and press Enter.
ACCOUNT                                           >>>>>>>>>>>> FILE = ACCOUNT
Type a value for variable "WH" and press Enter.
WHERE                                             >>>>>>>>>>>> WH = WHERE
Type a value for variable "COMP" and press Enter.
PARTY=                                             >>>>>>>>>>>> COMP = PARTY
Type a value for variable "VAL" and press Enter. 
'P00000000002'                                    >>>>>>>>>>>> VAL = ‘P0000000002’
 
Type reply, press Enter.
Reply . . .__________________________________________
________________________________________________________
 
 
F3=Exit   F12=Cancel

 

 

 

 

OUTPUT

 

 


                               Display Report
 
Query . . . . .:   AMIT/QUERYC_C5            Width . . .:       123
Form  . . . . .:   *SYSDFT                   Column  . .:         1
Control  . . . .
Line   ....+....1....+....2....+....3....+....4....+....5....+....6..
ORG CODE  ACC NUMBER    CURRENCY  PARTY NUM     A/C STS
--------  ------------  --------  ------------  -------
000001       190   100000000002  EUR       P00000000002       2
000002       190   100000000003  USD       P00000000002       2
******  * * * * *  E N D  O F  D A T A  * * * * *
 



 

           

III.            To run Static QMQRY from command line

 

The source code for the source member of source type QMQRY is as shown below:


Columns . . . :    1  80                  Browse                            AMIT/QRPGLESRC
SEU==>                                                                          QUERYCCCC
FMT **  ...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8
*************** Beginning of data ********************************************************
0001.00 SELECT ORG, ACC, CCY, ACCSTS FROM ACCOUNT
0002.00 WHERE RRN(ACCOUNT) IN
0003.00 (SELECT RRN(ACCOUNT) FROM ACCOUNT
0004.00 WHERE PARTY = 'P00000000002')
****************** End of data ************************************************************

 

Compile the source code and then start the query.


CRTQMQRY QMQRY(AMIT/QUERYCCCC) SRCFILE(AMIT/QRPGLESRC) SRCMBR(QUERYCCCC)
STRQMQRY QMQRY(AMIT/QUERYCCCC)      


 

OUTPUT



                             Display Report
 
Query . . . . .:   AMIT/QUERYCCCC            Width . . .:       123
Form  . . . . .:   *SYSDFT                   Column  . .:         1
Control  . . . .
Line   ....+....1....+....2....+....3....+....4....+....5....+....6
ORG CODE  ACC NUMBER    CURRENCY  PARTY NUM     A/C STS
--------  ------------  --------  ------------  -------
000001       190   100000000002  EUR       P00000000002       2
000002       190   100000000003  USD       P00000000002       2
******  * * * * *  E N D  O F  D A T A  * * * * *


 

 

IV.            Use *QRYDEF object to make *QMQRY type object

 

Type WRKQRY on command line and press ENTER.


                               Work with Queries                               
                                                                               
 Type choices, press Enter.                                                    
                                                                               
   Option  . . . . . .   1              1=Create, 2=Change, 3=Copy, 4=Delete   
                                        5=Display, 6=Print definition          
                                        8=Run in batch, 9=Run                  
   Query . . . . . . .   QUERY001       Name, F4 for list                      
     Library . . . . .     AMIT         Name, *LIBL, F4 for list               
                                                                               
                                                                               
                                                                             
                                                                                
                                                                               
                                                                               
 F3=Exit       F4=Prompt       F5=Refresh       F12=Cancel                      
                                                 (C) COPYRIGHT IBM CORP. 1988  

 

 

 

 

 

 

 


                            Specify File Selections                            
                                                                               
 Type choices, press Enter.  Press F9 to specify an additional                 
   file selection.                                                             
                                                                                
   File . . . . . . . . .   ACCOUNT        Name, F4 for list                   
     Library  . . . . . .     AMIT         Name, *LIBL, F4 for list            
   Member . . . . . . . .   *FIRST         Name, *FIRST, F4 for list           
   Format . . . . . . . .   *FIRST         Name, *FIRST, F4 for list           
                                                                               
                                                                                
                                                                               
                                                                               
                                                                             
                                                                                
                                                                               
 F3=Exit           F4=Prompt          F5=Report            F9=Add file         
 F12=Cancel        F13=Layout         F24=More keys                           


 

 

 


                          Select and Sequence Fields                           
                                                                               
 Type sequence number (0-9999) for the names of up to 500 fields to            
   appear in the report, press Enter.                                          
                                                                               
 Seq   Field                                                                    
 1     ORG                                                                     
 2     ACC                                                                     
 3     CCY                                                                      
 4     PARTY                                                                   
 5     ACCSTS                                                                  
       ENTSTS                                                                   
       VERSTS                                                                  
       AUTSTS                                                                  
       RECSTS                                                                   
       TIMESTP                                                                 
                                                                               
                                                                                
                                                                               
                                                                               
                                                                        Bottom 
 F3=Exit           F5=Report          F11=Display text       F12=Cancel        
 F13=Layout        F20=Renumber       F21=Select all         F24=More keys     


 

 

 


                                Define the Query                               
                                                                               
 Query . . . . . . :   QUERY001          Option  . . . . . :   CHANGE          
   Library . . . . :     AMIT            CCSID . . . . . . :   65535           
                                                                               
 Type options, press Enter.  Press F21 to select all.                          
   1=Select                                                                    
                                                                               
 Opt    Query Definition Option                                                
      > Specify file selections                                                
        Define result fields                                                   
      > Select and sequence fields                                              
      > Select records                                                         
        Select sort fields                                                     
        Select collating sequence                                               
        Specify report column formatting                                       
        Select report summary functions                                        
        Define report breaks                                                    
        Select output type and output form                                     
        Specify processing options                                             
                                                                                
 F3=Exit            F5=Report                                                  
 F13=Layout         F18=Files          F21=Select all                          
 Select options, or press F3 to save or run the query.                       

 


 

 


                                Display Report                                                           
 
 
Query . . . :   AMIT/QUERY001                                        Report width . . . . . :      46
Position to line  . . . . .                                          Shift to column  . . . . . .
Line   ....+....1....+....2....+....3....+....4....+.
ORG CODE  ACC NUMBER    CURRENCY  PARTY NUM     A/C STS
--------  ------------  --------  ------------  -------
000001       190   100000000002  EUR       P00000000002       2
000002       190   100000000003  USD       P00000000002       2
******  * * * * *  E N D  O F  D A T A  * * * * *

 

 

 


                               Work with Objects                               
                                                                               
 Type options, press Enter.                                                    
   2=Edit authority        3=Copy   4=Delete   5=Display authority   7=Rename  
   8=Display description   13=Change description                               
                                                                               
 Opt  Object      Type      Library     Attribute   Text                       
      QUERY001    *QRYDFN   AMIT        QRY                                    
                                                                               
                                                                               
                                                                                
                                                                               
                                                                               
                                                                                
                                                                         Bottom
 Parameters for options 5, 7 and 13 or command                                 
 ===>                                                                           
 F3=Exit   F4=Prompt   F5=Refresh   F9=Retrieve   F11=Display names and types  
 F12=Cancel   F16=Repeat position to   F17=Position to                    


 

===> RTVQMQRY QMQRY(AMIT/QUERY001) SRCFILE(AMIT/QRPGLESRC) SRCMBR(RETRIEVED) ALWQRYDFN(*YES)

 

 


Columns . . . :    1  80                   Browse                          AMIT/QRPGLESRC
SEU==>                                                                          RETRIEVED
FMT **  ...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8
*************** Beginning of data *******************************************************
0001.00 H QM4 05 Q 01 E V W E R 01 03 13/13/02 07:21
0002.00 V 1001 050
0003.00 V 5001 004 *HEX
0004.00 SELECT
0005.00   ALL       ORG, ACC, CCY, PARTY, ACCSTS
0006.00   FROM      AMIT/ACCOUNT T01
0007.00   WHERE     PARTY = 'P00000000002'
****************** End of data **********************************************************
 


 

>>>> Here you need to decide which part you want to make as dynamic and change that part with the variable name and pass the value in that variable in CL program as explained in the first example.

 

 

 

 

 

 

 

V.            Save the output of QMQRY in *OUTFILE

 

QMQRY source


Columns . . . :    1  80                       Browse                    AMIT/QRPGLESRC
SEU==>                                                                        QUERYC_C5
FMT **  ...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8
*************** Beginning of data *****************************************************
0001.00   &SEL  &FLD  &FR
0002.00   &FILE
0003.00   &WH
0004.00   &COMP &VAL
****************** End of data ********************************************************

 

CL program saving the output of QMQRY in a file OUTFILE001


Columns . . . :    1  80                  Edit                             AMIT/QRPGLESRC
SEU==>                                                                         QUERYCL_C6
FMT **  ...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8
*************** Beginning of data *******************************************************
0001.00 PGM
0002.00              DCL        VAR(&SEL_Q) TYPE(*CHAR) LEN(8) VALUE(SELECT)
0003.00              DCL        VAR(&FLD_Q) TYPE(*CHAR) LEN(30) +
0004.00                         VALUE('ORG, ACC, CCY, PARTY, ACCSTS')
0005.00              DCL        VAR(&FR_Q) TYPE(*CHAR) LEN(10) VALUE('FROM')
0006.00              DCL        VAR(&FILE_Q) TYPE(*CHAR) LEN(10) +
0007.00                           VALUE('ACCOUNT')
0008.00              DCL        VAR(&WH_Q) TYPE(*CHAR) LEN(7) VALUE('WHERE')
0009.00              DCL        VAR(&COMP_Q) TYPE(*CHAR) LEN(6) VALUE('PARTY=')
0010.00              DCL        VAR(&VAL_Q) TYPE(*CHAR) LEN(15) +
0011.00                           VALUE('''P00000000002''')
0012.00              MONMSG MSGID(CPF0000) EXEC(GOTO  ERROR)
0013.00 /*_______________________________________________________________*/
0014.00
0015.00              STRQMQRY   QMQRY(AMIT/QUERYC_C5) OUTPUT(*OUTFILE) +
0016.00                           OUTFILE(AMIT/OUTFILE001) OUTMBR(*FIRST +
0017.00                           *ADD) SETVAR((SEL &SEL_Q) (FLD &FLD_Q) +
0018.00                           (FR &FR_Q) (FILE &FILE_Q) (WH &WH_Q) +
0019.00                           (COMP &COMP_Q) (VAL &VAL_Q))
0020.00              GOTO       CMDLBL(END)
0021.00 ERROR:
0022.00              SNDUSRMSG  MSG('SOME ERROR IN CALLING THE QMQUERY')
0023.00 END:
0024.00 ENDPGM
****************** End of data ********************************************************


 

 

 

>>>>  RUNQRY *N OUTFILE001         

 

         


                            Display Report  
                                                        
                                                              Report width . . . . . :      55 
 Position to line  . . . . .                                   Shift to column  . . . . . .         
 Line   ....+....1....+....2....+....3....+....4....+....5....+
ORG CODE  ACC NUMBER    CURRENCY  PARTY NUM     A/C STS
000001    190    100000000002    EUR     P00000000002     2
000002    190    100000000003    USD     P00000000002     2
****** ********  End of report  ********

 

 

 

 

 

**NOTE

 

RUNSQLSTM can run many statements (using ';' between each one) but cannot use parameters.

STRQMQRY can run only one statement but can use parameter.

 





User Comments:





Copyright © Go4as400.com, 2013-2017. Copyright notice