A Complex Join Logical File (Example 10)

The following example shows a more complex join logical file. Assume the data is in the following three physical files: 



Vendor Master File (PF1)
|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8
     A          R RCD1                      TEXT('VENDOR INFORMATION')
     A            VDRNBR         5          TEXT('VENDOR NUMBER')
     A            VDRNAM        25          TEXT('VENDOR NAME')
     A            STREET        15          TEXT('STREET ADDRESS')
     A            CITY          15          TEXT('CITY')
     A            STATE          2          TEXT('STATE')
     A            ZIPCODE        5          TEXT('ZIP CODE')
     A                                      DFT('00000')
     A            PAY            1          TEXT('PAY TERMS')
     A
 
Order File (PF2)
|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8
     A          R RCD2                      TEXT('VENDORS ORDER')
     A            VDRNUM         5S 0       TEXT('VENDOR NUMBER')
     A            JOBNBR         6          TEXT('JOB NUMBER')
     A            PRTNBR         5S 0       TEXT('PART NUMBER')
     A                                      DFT(99999)
     A            QORDER         3S 0       TEXT('QUANTITY ORDERED')
     A            UNTPRC         6S 2       TEXT('PRICE')
     A
 
Part File (PF3)
|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8
     A          R RCD3                      TEXT('DESCRIPTION OF PARTS')
     A            PRTNBR         5S 0       TEXT('PART NUMBER')
     A                                      DFT(99999)
     A            DESCR         25          TEXT('DESCRIPTION')
     A            UNITPRICE      6S 2       TEXT('UNIT PRICE')
     A            WHSNBR         3          TEXT('WAREHOUSE NUMBER')
     A            PRTLOC         4          TEXT('LOCATION OF PART')
     A            QOHAND         5          TEXT('QUANTITY ON HAND')
     A

The join logical file record format should contain the following fields:

Vdrnam (vendor name)
Street, City, State, and Zipcode (vendor address)
Jobnbr (job number)
Prtnbr (part number)
Descr (description of part)
Qorder (quantity ordered)
Untprc (unit price)
Whsnbr (warehouse number)
Prtloc (location of part)

The DDS for this join logical file is as follows: 



Join Logical File (JLF)
|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8
      A                                 (1)  DYNSLT
      A                                 (2)  JDFTVAL
      A          R RECORD1                   JFILE(PF1 PF2 PF3)
      A     (3)  J                           JOIN(1 2)
      A                                      JFLD(VDRNBR VDRNUM)
      A                                 (4)  JDUPSEQ(JOBNBR)
      A     (5)  J                           JOIN(2 3)
      A                                 (6)  JFLD(PRTNBR PRTNBR)
      A                                      JFLD(UNTPRC UNITPRICE)
      A       (7)  VDRNUM         5A  N      TEXT('CHANGED ZONED TO CHAR')
      A            VDRNAM
      A            ADDRESS              (8)  CONCAT(STREET CITY STATE +
      A                                       ZIPCODE)
      A            JOBNBR
      A            PRTNBR                (9)  JREF(2)
      A            DESCR
      A            QORDER
      A            UNTPRC
      A            WHSNBR
      A            PRTLOC
      A    (10)  S VDRNAM                    COMP(EQ 'SEWING COMPANY')
      A          S QORDER                    COMP(GT 5)
      A

(1)
The DYNSLT keyword is required because the JDFTVAL keyword and select fields are specified.

(2)
The JDFTVAL keyword is specified to pick up default values in physical files.

(3)
First join specification.

(4)
The JDUPSEQ keyword is specified because duplicate vendor numbers occur in PF2.

(5)
Second join specification.

(6)
Two JFLD keywords are specified to ensure the correct records are joined from the PF2 and PF3 files.

(7)
The Vdrnum field is redefined from zoned decimal to character (because it is used as a join field and it does not have the same attributes in PF1 and PF2).

(8)
The CONCAT keyword concatenates four fields from the same physical file into one field.

(9)
The JREF keyword must be specified because the Prtnbr field exists in two physical files and you want to use the one in PF2.

(10)
The select/omit fields are Vdrnam and Qorder. (Note that they come from two different physical files.)

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]