Ü 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.