Ordering by sub string of a field with OPNQRYF

Question

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?

Answer 1

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.