Ü Open
query file
·
OPNQRYF
command creates a temporary access path for a file and after its use the access
path is discarded.
·
We change
the access path of a file dynamically with the help of this command.
·
It can be
used to select a subset of the available records, order the records, group the
records, join the records.
·
Before we
go through open query file we should understand open data path also.
Ü Open Data Path
·
This is a
temporary object which is used to create dynamic access path for a file.
·
Access path
describes the order in which records are to be read.
·
Access
paths can be kept on the system permanently (such as physical or logical file)
or temporarily (OPNQRYF). OPNQRYF command creates a temporary access path for
use one time, and then discard the access path.
·
The open
data path contains the information like file name, format name, current record
pointer, record selection information etc.
·
Open data
path has only one cursor. Each program that shares the same ODP has only one
image of the cursor. So if the cursor is repositioned in the called program
then the cursor will still be pointing at the same repositioned record.
·
Languages
other than CL cannot make dynamic access path or in other words cannot perform
dynamic record selection. If we want other programs like RPG also to perform
dynamic record selection, we can share the access path of the file (that we
create in CL) with the RPG program.
·
To share
the access path we create an open data path in our CL program with OPNQRYF
command and then we share this ODP with the called RPG program. This sharing is
done with the help of SHARE (*YES) in the OVRDBF command.
Ü Steps to create and use an OPNQRYF
I.
OVRDBF
ü
FILE (file PF) TOFILE (LIB/ PF)
SHARE (*Yes)
II.
OPNQRYF
OPNQRYF FILE(lib
name/file name +
Member-name +
Record-format-name) +
OPTION(open-option) +
FORMAT(lib
name/database file name +
Record-format name) +
QRYSLT(query
selection) +
KEYFLD(field
name)
The attribute of OPNQRYF is described below:
·
FILE : File to be
processed.
·
OPTION: In which mode the file is to be
processed.
OPTION(*INP *OUT
*UPD *DLT *ALL)
*ALL is
combination of first four.
·
FORMAT: File
format
·
QRYSLT:
ü
*ALL
FILE (LIB
/ PF) QRYSLT(*ALL)
It includes all records.
ü
*BCAT
FILE (LIB / PF) QRYSLT
(‘EMPNO *EQ ‘ *BCAT &A)
We can
use *BCAT to insert the variable’s value in an expression with blanks in
between.
ü
%WLDCRD
It is similar to %LIKE in SQL
QRYSLT (‘PARTY_NAME *EQ %WLDCRD (“S* “)’)
It
will fetch all the records where party name starts from S.
ü
*CT
It will fetch all the records, which contains
the particular character.
QRYSLT (‘PARTY_NAME *CT “S” ‘)
It will fetch all the records where
party name contains the character ‘S’.
ü
%RANGE
It will fetch the records within the specific
range
QRYSLT (‘PARTY_NUM *EQ
%RANGE (10000001 20000001)’)
It will fetch all the records where
party number falls in the range 10000001 and 20000001.
·
KEYFLD:
OPNQRYF FILE(ACCOUNT)
QRYSLT(‘ CURRENCY = “USD” ’)
KEYFLD((ORG-CODE
*DESCEND) (ACCOUNT))
III.
CALL PGM (LIB/PGM-NAME)
PARM ()
IV.
DLTOVR
Ø
DLTOVR FILE (OPNPF)
V.
CLOF
Ø
CLOF OPNID (OPNPF)
Example
Below
is the CL program that first of all creates one ODP and then shares it with the
called program ORG190.
Columns . . . : 1 100 Edit IROBO1/QRPGLESRC
SEU==> OPNQRYF_C2
FMT ** ...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8 ...
*************** Beginning of data **************************************************
0001.00 PGM
0002.00 DCL VAR(&VAR1) TYPE(*CHAR) LEN(35)
0003.00 DCL VAR(&CHAR1) TYPE(*CHAR) LEN(3)
0004.00 DCL VAR(&REPLY) TYPE(*CHAR) LEN(1) +
0005.00 VALUE('N')
0006.00 DCLF FILE(IROBO1/ACCOUNT)
0007.00 MONMSG MSGID(CPF0000) EXEC(GOTO CMDLBL(END2))
0008.00 READ:
0009.00 OVRDBF FILE(ACCOUNT) TOFILE(IROBO1/ACCOUNT) SHARE(*YES)
0010.00 OPNQRYF FILE((ACCOUNT)) OPTION(*ALL) QRYSLT('ORG *EQ +
0011.00 190') OPNID(ID1)
0012.00 CONTINUE:
0013.00 CALL PGM(ORG190)
0014.00 SNDUSRMSG MSG('THE END OF FILE IS REACHED')
0015.00 END2:
0016.00 DLTOVR FILE(ACCOUNT)
0017.00 CLOF OPNID(ID1)
0018.00 ENDPGM
****************** End of data ***************************************************
Called
Program ORG190
Columns . . . : 1 100 Browse IROBO1/QRPGLESRC
SEU==> ORG190
FMT FX .....FFilename++IPEASF.....L.....A.Device+.Keywords+
*************** Beginning of data ******************
0001.00 FACCOUNT IF E K DISK
0002.00 FDSP2 CF E WORKSTN
0003.00 *
0004.00 C DOW *IN03=*OFF
0005.00 C 03 LEAVE
0006.00 C READ ACCOUNT
0007.00 C IF %EOF(ACCOUNT)
0008.00 C LEAVE
0009.00 C ENDIF
0010.00 C EVAL S_PARTY=PARTY
0011.00 C EVAL S_ORG=ORG
0012.00 C EVAL S_ACC=ACC
0013.00 C EVAL S_CCY=CCY
0014.00 C
0015.00 C WRITE HEADER
0016.00 C WRITE DETAIL
0017.00 C WRITE FOOTER
0018.00 C READ HEADER
0019.00 C ENDDO
0020.00 C SETON LR
****************** End of data **********************************
Display
file used in the program ORG190
Columns . . . : 1 100 Edit IROBO1/QRPGLESRC
SEU==> DSP2
FMT A* .....A*. 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8 ..
*************** Beginning of data *************************************************
0000.30 A DSPSIZ(24 80 *DS3)
0000.40 A CA03(03 'EXIT')
0000.50 A R HEADER
0000.60 A 3 27'ACCOUNT REALATED INFORMATION'
0000.70 A 3 1USER
0000.80 A 3 73DATE
0000.90 A EDTCDE(Y)
0001.00 A 4 73TIME
0001.10 A R DETAIL
0001.30 A OVERLAY
0001.40 A 7 14'PARTY NUMBER....'
0001.50 A 8 14'ORG CODE........'
0001.60 A 9 14'ACCOUNT NUMBER..'
0001.70 A 10 14'CURRENCY........'
0001.80 A S_PARTY 12A O 7 34
0001.90 A S_ORG 3S 0O 8 34
0002.00 A S_ACC 12A O 9 34
0002.10 A S_CCY 3A O 10 34
0002.20 A R FOOTER
0002.30 A OVERLAY
0002.40 A 23 2'F3 = EXIT'
****************** End of data ******************************************************
DATABASE
File used in the program: ACCOUNT
ORG ACC CCY PARTY
000001 190 A00000000001 CCY P00000000001
000002 191 A00000000002 EUR P00000000002
000003 192 A00000000003 USD P00000000003
000004 192 A00000000004 TRY P00000000004
000005 190 A00000000005 INR P00000000005
000006 190 A00000000006 TRY P00000000006
000007 192 A00000000007 INR P00000000007
000008 191 A00000000008 GBP P00000000002
000009 191 A00000000009 USD P00000000001
000010 195 A00000000010 EUR P00000000003
000011 193 A00000000011 TRY P00000000005
000012 192 A00000000012 EUR P00000000002
000013 190 A00000000013 EUR P00000000009
000014 191 P00000000014 USD P00000000004
000015 192 A00000000015 INR P00000000010
000016 192 A00000000016 GBP P00000000011
000017 192 A00000000017 AZK P00000000017
000018 191 A00000000018 EUR P00000000014
000019 190 P00000000019 EUR P00000000015
OUTPUT
IROBO ACCOUNT REALATED INFORMATION 4/28/13
12:59:20
PARTY NUMBER.... P00000000001
ORG CODE........ 190
ACCOUNT NUMBER.. A00000000001
CURRENCY........ CCY
F3 = EXIT
We
press Enter to get the second record.
IROBO ACCOUNT REALATED INFORMATION 4/28/13
13:00:47
PARTY NUMBER.... P00000000005
ORG CODE........ 190
ACCOUNT NUMBER.. A00000000005
CURRENCY........ INR
F3 = EXIT
Example-II: Using CPYFRMQRYF
We
can copy overridden file’s selected records using CPYFRMQRYF.
·
CPYFRMQRYF
ü
To see the records being selected
from the query file, we have to copy from it from the overridden file to a
temporary file.
CPYFRMQRYF FROMOPNID
(OPN_ID) TOFILE (LIB/Target File Name) MBR (*REPLACE) CRTFILE (*YES) FMTOPT
(*NOCHK)
·
RUNQRY
ü
We have copied the contents satisfy
the query into a temporary file using CPYFRMQRYF. If we run the target file we
get the records copied, which satisfy the query.
RUNQRY
QRYFILE (LIB/Target File Name)
Columns . . . : 1 100 Browse IROBO1/QRPGLESRC
SEU==> OPNQRYF_C3
FMT ** ...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8 ..
*************** Beginning of data ******************************************************
0001.00 PGM
0002.00 DCL VAR(&VAR1) TYPE(*CHAR) LEN(35)
0003.00 DCL VAR(&CHAR1) TYPE(*CHAR) LEN(3)
0004.00 DCL VAR(&REPLY) TYPE(*CHAR) LEN(1) +
0005.00 VALUE('N')
0006.00 DCLF FILE(IROBO1/ACCOUNT)
0007.00 MONMSG MSGID(CPF0000) EXEC(GOTO CMDLBL(END2))
0008.00 READ:
0009.00 OVRDBF FILE(ACCOUNT) TOFILE(IROBO1/ACCOUNT) SHARE(*YES)
0010.00 OPNQRYF FILE((ACCOUNT)) OPTION(*ALL) QRYSLT('ORG *EQ +
0011.00 190') OPNID(ID1)
0012.00
0013.00 CPYFRMQRYF FROMOPNID(ID1) TOFILE(QTEMP/RESULT) +
0014.00 MBROPT(*REPLACE) CRTFILE(*YES)
0015.00 END2:
0016.00 DLTOVR FILE(ACCOUNT)
0017.00 CLOF OPNID(ID1)
0018.00 RUNQRY QRYFILE((QTEMP/RESULT)) OUTTYPE(*DISPLAY)
0019.00 ENDPGM
****************** End of data ******************************************************
Display Report
Report width . . . . . : 37
Position to line . . . . . Shift to column . . . . . .
Line ....+....1....+....2....+....3....+..
ORG ACC CCY PARTY
000001 190 A00000000001 CCY P00000000001
000002 190 A00000000005 INR P00000000005
000003 190 A00000000006 TRY P00000000006
000004 190 A00000000013 EUR P00000000009
000005 190 P00000000019 EUR P00000000015
****** ******** End of report ********
Bottom
F3=Exit F12=Cancel F19=Left F20=Right F21=Split F22=Width 80
Example-III: Using OPNQRYF for
dynamic join of physical files
OVRDBF FILE(CAFI03C0) TOFILE(*LIBL/CZGI03A0) +
SHARE(*YES)
OPNQRYF FILE((CZGI03A0 *FIRST CZTI03A2) +
(CZGD02A0 *FIRST CZTD02A2)) +
FORMAT(CAFI03C0) +
JDFTVAL(*YES) +
JFLD((1/I03LVLIDN 2/D02LVLIDN) +
(1/I03ORGCODA 2/D02ORGCOD) +
(1/I03ACNTNO 2/D02ACNTNO)) +
GRPFLD((I03LVLIDN) +
(I03ORGCODA) +
(D02ACTYPE) +
(I03SUBCOD) +
(I03MKTCOD) +
(I03LVLIDND ) +
(I03ORGCODD ) +
(I03TXTYPE ) +
(I03PSTDATY ) +
(I03PSTDATM ) +
(I03LCYAMTC) +
(I03LCYAMTM)) +
QRYSLT('(I03PRCDATY = ' || &PRCDATY || ') +
*AND +
(I03PRCDATM = ' || &PRCDATM || ') +
*AND +
(I03LCYAMT < 0)') +
MAPFLD((I03TOTAMT '%SUM(I03LCYAMT)') +
(I03COUNT '%COUNT')) +
KEYFLD((1/I03LVLIDN) +
(1/I03ORGCODA) +
(2/D02ACTYPE) +
(1/I03SUBCOD) +
(1/I03MKTCOD ) +
(1/I03LVLIDND) +
(1/I03ORGCODD) +
(1/I03TXTYPE) +
(1/I03PSTDATY) +
(1/I03PSTDATM)