Example 10: A complex join logical file

 

This example shows a more complex join logical file.

Assume that 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 data description specifications (DDS) for this join logical file are shown 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 that 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.

They come from two different physical files.)

 

Parent topic:

Setting up a join logical file