AW: OPNQRYF Question

[Werner Noll <werner.noll@xxxxxxxxx>]

Janell,

if you want to join data from different physical files in one record format,
you can do that easily by OPNQRF (without creating a Join-LF before).
You just specify a DDS for a PF with all fields you need for processing and
then create the PF. With the join option (JFLD) in the OPNQRYF command you
join the files together and you data will be supplied in the specified
record format. This format can only be processed as input. For updates you
must chain to the appropriate PFs or LFs.

Here an example. The join-format is SRQSHA, which contains fields from 3
different PFs. See the DDS below.

/*  SHIPMENT HEADER DATA                                            */
             OVRDBF     FILE(SRQSHA) TOFILE(SRPSH) SHARE(*YES)
             OPNQRYF    FILE((SRPSH) (SRPSL) (SRPPL)) +
                          FORMAT(SRQSHA)       +
                          QRYSLT('SHGT *EQ "' *CAT &GATE *CAT '"  +
                           *AND SLFSID *LT "1" +
                           *AND SLSEQ  *EQ 0') +
                          KEYFLD((SHCC)   +
                                 (SHGT)   +
                                 (SHBC)   +
                                 (SHCPN)  +
                                 (SHPTY)  +
                                 (SHDDTT) +
                                 (SHDLDT) +
                                 (SHCNDP) +
                                 (SHSHP)  +
                                  (PLLOC)  +
                                  (SLLIN)  +
                                  (SLSEQ)) +
                           JFLD((SRPSH/SHCC  SRPSL/SLCC)  +
                                (SRPSH/SHSHP SRPSL/SLSHP) +
                                (SRPSH/SHCC  SRPPL/PLCC)  +
                                (SRPSH/SHGT  SRPPL/PLGT)  +
                                (SRPSL/SLMC  SRPPL/PLMC)) +
                           JDFTVAL(*YES)

 /*  WRITE PICKINGLIST                                               */
              CALL       PGM(SRR128) PARM(&P$GASS)

              CLOF       OPNID(SRLSHI)
              DLTOVR     FILE(SRQSHA)

SRQSHA is the format with selected fields out from the files  SRPSH, SRPSL
and SRPPL. The DDS is like follows:

A          R SRQSHAR
 **********************************************************
 *  SRMS SHIPMENT HEADER
A            SHCC      R               REFFLD(SHCC   SRPSH)
A            SHSHP     R               REFFLD(SHSHP  SRPSH)
A            SHGT      R               REFFLD(SHGT   SRPSH)
A            SHBC      R               REFFLD(SHBC   SRPSH)
A            SHCPN     R               REFFLD(SHCPN  SRPSH)
A            SHPTY     R               REFFLD(SHPTY  SRPSH)
A            SHDDTT    R               REFFLD(SHDDTT SRPSH)
A            SHDLDT    R               REFFLD(SHDLDT SRPSH)
A            SHCNDP    R               REFFLD(SHCNDP SRPSH)
 *  SRMS PICK LOCATION FILE
A            PLLOC     R               REFFLD(PLLOC  SRPPL)
 *  SRMS SHIPMENT LINE
A            SLLIN     R               REFFLD(SLLIN  SRPSL)
A            SLSEQ     R               REFFLD(SLSEQ  SRPSL)
A            SLMC      R               REFFLD(SLMC   SRPSL)
A            SLMCQT    R               REFFLD(SLMCQT SRPSL)
A            SLPLU     R               REFFLD(SLPLU  SRPSL)
A            SLRQID    R               REFFLD(SLRQID SRPSL)
A            SLMSID    R               REFFLD(SLMSID SRPSL)
A            SLQFID    R               REFFLD(SLQFID SRPSL)
A            SLFSID    R               REFFLD(SLFSID SRPSL)
A            SLMPF     R               REFFLD(SLMPF  SRPSL)
A            SLEAN     R               REFFLD(SLEAN  SRPSL)
A            SLEEAN    R               REFFLD(SLEEAN SRPSL)
A            SLCPR1    R               REFFLD(SLCPR1 SRPSL)
A            SLCPR2    R               REFFLD(SLCPR2 SRPSL)
A            SLLPR     R               REFFLD(SLLPR  SRPSL)
A            SLDLQT    R               REFFLD(SLDLQT SRPSL)
A            SLCRC     R               REFFLD(SLCRC  SRPSL)


Hope this helps.
Regards,
Werner Noll


-----Ursprüngliche Nachricht-----
Von: Janell Haskins [mailto:jhaskins@knitrite.com]
Gesendet: Donnerstag, 31. Oktober 2002 15:35
An: rpg400-l@midrange.com
Betreff: OPNQRYF Question


I recently "discovered" OPNQRYF and I'm trying to figure out how (or if) it
I can use it in my current programming project.

Most of the information I need is in a detail file, however, two very
important pieces of information (the transaction date and the transaction
type) are only in a header file.  I was going to create a logical file that
joined the two physical files and then let the RPG program determine which
records to include in the report, based on parameters entered by the user.

I know that I can use OPNQRYF to select the desired records before the RPG
program starts.  The problem I have is that OPNQRYF will only open a join
file in input mode (*INP) and what I need is output mode (*OUT).  At least
that's how I read the IBM manual.

So here's my question, can I use OPNQRYF to select the desired records from
the physical files and still use the logical join file to access them from
my RPG program?  If not, do you have any other suggestions of what I could
try?  (I'm sure that this could be done with SQL, but I know even less about
how to do that than I do about OPNQRYF.)

Finally, could you suggest a good resource(s) for learning more about
OPNQRYF?

Thanks!

Janell Haskins
Programmer/Analyst
Knit-Rite, Inc.
(913) 281-4600, ext. 113

_______________________________________________
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/rpg400-l
or email: RPG400-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.