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

 











User Comments:



Copyright © www.go4as400.com, 2013-2023. Copyright notice   Terms of services   Privacy policy