CODEWITHSUNDEEP
sqlibm-midrangerpgle
theju112
theju112

Reputation: 91

Ordering by sub string of a field with OPNQRYF

I have a requirement where I need to change the order in which records are printed in a Report. I need to order the records by a substring of a field of the records.

There is an OPNQRYF as below before the call to the report RPG is made:

 OVRDBF     FILE(MOHDL35) SHARE(*YES)                

 BLDQRYSLT  QRYSLT(&QRYSLT)                  +       
   SELECT((CHARDT *GE &FRDATE  F2)  +                
   (CHARDT *LE &TODATE  F2)  +                       
   (HDPLVL *EQ 'FS'     F2)  +                       
   (HDMPLT *EQ &PLANT   F2))                         

      OPNQRYF    FILE((*LIBL/MOHDL35))       +       
                 QRYSLT(&QRYSLT)             +       
                 KEYFLD(*FILE)               +       
                 MAPFLD((ZONEDT HDAEDT *ZONED 8 0) + 
                        (CHARDT ZONEDT *CHAR 8))

One way I see how to do this is to do a RUNSQL to create a temp table in qtemp with the MOHDL35 records in the required order. The substr SQL function would help to achieve this much easier. This should have the same structure as that of MOHDL35 (FIELD NAMES, RECORD FORMAT)

Then replace the use of this file in the RPG program with the newly created table name. I havent tried this yet, but would this work? does it sound like a good idea? Are there any better suggestions?

Upvotes: 2

Views: 911

Answers (2)

jmarkmurphy
jmarkmurphy

Reputation: 11473

You can do that with OPNQRYF by using the MAPFLD parameter like this:

OPNQRYF FILE((JCVCMP))                  
        KEYFLD((*MAPFLD/PART))          
        MAPFLD((PART '%SST(VCOMN 2 5)'))

The fields in JCVCOMN are now sorted like this:

VENNO  VCMTP  VCMSQ  VCOMN
-----  -----  -----  -------------------------
1,351  ICL    3      Let's see what wow
1,351  ICL    1      This is a test
1,351  NDA    2      another comment
1,351  NDA    1      more records

Notice that the records are sorted by the substring of VCOMN starting with the second character.

So here is your OPNQRYF with multiple key fields specified

OPNQRYF FILE((*LIBL/MOHDL35))                               
        QRYSLT(&QRYSLT)                                     
        KEYFLD((*MAPFLD/CHARDT) (*MAPFLD/HDPROD))           
        MAPFLD((ZONEDT HDAEDT *ZONED 8 0) (CHARDT ZONEDT *CHAR 8)
               (HDPROD '%SST(HDPROD 1 2) *CAT %SST(HDPROD 10 12) 
               *CAT %SST(HDPROD 13 16)'))

Some notes: I am guessing that HDAEDT is a PACKED number. If so, you don't need to map it to a ZONED number just to get it to a character value. If you need the ZONED value, that is ok (but PACKED should work just as well). Otherwise, you can just use:

MAPFLD((CHARDT HDAEDT *CHAR 8))

Also in your OVRDBF, you need to make sure you choose the correct Override Scope OVRSCOPE. The IBM default is OVRSCOPE(*ACTGRPDFN). OPNQRYF also has a scope OPNSCOPE. You need to make sure that the OVRSCOPE, the OPNSCOPE, and the program using the table all use the same activation group. There are a lot of different combinations. If you can't make it work, you can always change them all to *JOB, and that will work. But there is nothing intrinsic about OPNQRYF that prevents it from working from a CLP.

Upvotes: 1

MandyShaw
MandyShaw

Reputation: 1156

I would try creating a view with all the table fields plus the substring'd column, and then use OPNQRYF with that instead of the table, specifying the substring'd column as the KEYFLD. That would probably be simpler (& potentially quicker) than copying the whole lot into QTEMP every time.

Upvotes: 0

Related Questions