AS/400 CLLE: What is open query

450 pts.
Tags:
AS 400
CLLE
What is open query and when we use open query? How we use it in CL program?

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Discuss This Question: 6  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • ToddN2000
    @vivekntt21: You seem to be asking a lot of basic level AS/400, i-Series questions. Are you currently taking classes or trying to learn this on your own? If you do not have the books there are a lot of online manuals you can use for reference. An open query is basically creating a new view of data in a file for processing. You may want to create one to select certain records for example. This can come in handy if there is no current LF in the format you need.
    111,175 pointsBadges:
    report
  • azohawk
    There is even a book "Open Query File Magic" by Ted Holt. I believe it was first published several years ago, but this hasn't really changed much for getting started.
    3,685 pointsBadges:
    report
  • ToddN2000
    Something simple like select just a few warehouse for processing
    
    OPNQRYF  FILE((IS01)) FORMAT(*LIBL/IS01) QRYSLT('(ISWH +
                 *GE "XA" *AND ISWH *LE "Z9")') +           
                 KEYFLD((ISWH) (ISPART))                    
    
    
    to much more involved and even more but don't get to crazy.
    If they start getting to hard to maintain I would create a LF.
    
    
    OPNQRYF    FILE((ARP)) FORMAT(*LIBL/AR001W) +                 
               QRYSLT('(ARDFLG *EQ " "                           +
                       *OR APDT *LE ' || &DTYMD || '             +
                       *OR ARLACT *EQ 0                          +
                       *OR ARORG$ *NE ARAPP$)                    +
                    *AND (INVDT *LE ' || &DTYMD || '             +
                      *OR ARIVCT *EQ 0)')                        +
               MAPFLD((APDT  '(ARLAYY*10000+ARLAMM)' *DEC 6 0)   +
                      (INVDT '(ARIVYY*10000+ARIVMM)' *DEC 6 0)   +
                      (CDDRC  'ARDFLG')                          +
                      (CDCKNO 'ARINV#')                          +
                      (CDAPPM 'ARLAMM')                          +
                      (CDAPPY 'ARLAYY')                          +
                      (CDAPPC 'ARLACT'))                         +
               KEYFLD((ARDIV) (ARBTKY) (ARINV#) (ARORG$ *DESCEND))
    111,175 pointsBadges:
    report
  • WoodEngineer
    OPNQRYF was added by IBM a long time ago as an alternative to SQL. It can be rather complex to implement. It works well but can be rather tedious to code.

    If your development environment has SQL recommend using it instead. Much more robust, flexible and simpler to use.
    8,090 pointsBadges:
    report
  • TheRealRaven
    OPNQRYF was brought to the AS/400 line out of the System/38. It provides a kind of way to create a temporary VIEW, potentially a keyed view. It's been practically obsolete since maybe 1990 when SQL made a start on the systems.

    For "When do we use it?", we don't. There have long been better ways to do what it does in almost all cases.
    27,570 pointsBadges:
    report
  • WoodEngineer
    Here is a example of a simple OPNQRYF from a CL program. The primary purpose of this is to deliver the data to the program in a sequence which was rarely used. Because of that we did not want to add a logical file.

    OVRDBF     FILE(PMPRLCP) SHARE(*YES)                  
    OPNQRYF    FILE((PMPRLCP)) QRYSLT('cmp#pb = 01') +    
                 KEYFLD((SPCCPB) (USGCPB) (GRDCPB)) +     
                 UNIQUEKEY(*ALL)                          
    call your program
    DLTOVR FILE(PMPRLCP)
    8,090 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: