Example 3: Reading duplicate records in the secondary file

 

This example shows how a join logical file reads duplicate records in the secondary file based on the specification of the Join Duplicate Sequence (JDUPSEQ) DDS keyword.

The data description specifications (DDS) for the physical files and for the join logical file are shown as follows:

JLF
|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8
     A          R JREC                      JFILE(PF1 PF2)
     A          J                           JOIN(PF1 PF2)
     A                                      JFLD(NAME1 NAME2)
     A                                      JDUPSEQ(TELEPHONE)
     A            NAME1
     A            ADDR      A            TELEPHONE      A  

PF1
|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8
     A          R REC1
     A            NAME1         10
     A            ADDR          20
     A  

PF2
|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8
     A          R REC2
     A            NAME2         10
     A            TELEPHONE      8
     A

The physical files have the following records.

Table 1. Physical file 1 (PF1)

Name	Address
Anne	120 1st St.
Doug	40 Pillsbury
Mark	2 Lakeside Dr.

Table 2. Physical file 2 (PF2)

Name	Telephone
Anne	555–1111
Anne	555–6666
Anne	555–2222
Doug	555–5555

The join logical file returns the following records.

Table 3. Join logical file (JLF)

Name	Address	Telephone
Anne	120 1st St.	555–1111
Anne	120 1st St.	555–2222
Anne	120 1st St.	555–6666
Doug	40 Pillsbury	555–5555

The program reads all the records available for Anne, then Doug, then Mark. Anne has one address, but three telephone numbers. Therefore, there are three records returned for Anne.

The records for Anne sort in ascending sequence by telephone number because the JDUPSEQ keyword sorts in ascending sequence unless you specify *DESCEND as the keyword parameter. The following example shows the use of *DESCEND in DDS:

JLF
|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8
     A          R JREC                      JFILE(PF1 PF2)
     A          J                           JOIN(PF1 PF2)
     A                                      JFLD(NAME1 NAME2)
     A                                      JDUPSEQ(TELEPHONE *DESCEND)
     A            NAME1
     A            ADDR      A            TELEPHONE      A

When you specify JDUPSEQ with *DESCEND, the records are returned as follows.

Table 4. Join logical file (JLF)

Name	Address	Telephone
Anne	120 1st St.	555–6666
Anne	120 1st St.	555–2222
Anne	120 1st St.	555–1111
Doug	40 Pillsbury	555–5555

The JDUPSEQ keyword applies only to the join specification in which it is specified.

 

Parent topic:

Setting up a join logical file

 

Related reference

Example 10: A complex join logical file