Example 7: Joining three or more physical files

 

This example shows how to use a join logical file to join three or more physical files.

You can use a join logical file to join as many as 32 physical files. These files must be specified on the JFILE keyword. The first file specified on the JFILE keyword is the primary file; the other files are all secondary files.

The physical files must be joined in pairs, with each pair described by a join specification. Each join specification must have one or more join fields identified.

The following chart shows the fields in the files and one field common to all the physical files in the logical file.

The join logical file (JLF2) contains Name, Address, Telephone, and Salary. Physical file 1 (PF1) has Name and Address, physical file 2 (PF2) has Name and Telephone, and physical file 3 (PF3) has Name and Salary. In this example, the Name field is common to all the physical files (PF1, PF2, and PF3), and serves as the join field.

The following example shows the data description specifications (DDS) for the physical and logical files:

JLF
|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8
     A          R JOINREC                   JFILE(PF1 PF2 P3)
     A          J                           JOIN(PF1 PF2)
     A                                      JFLD(NAME NAME)
     A          J                           JOIN(PF2 PF3)
     A                                      JFLD(NAME NAME)
     A            NAME                      JREF(PF1)
     A            ADDR      A            TELEPHONE      A            SALARY      A          K NAME      A  

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

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

PF3
|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8
     A          R REC3
     A            NAME          10
     A            SALARY         9  2
     A          K NAME      A

Assume that 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.
Tom	335 Elm St.

Table 2. Physical file 2 (PF2)

Name	Telephone
Anne	555–1111
Doug	555–5555
Mark	555–0000
Sue	555–3210

Table 3. Physical file 3 (PF3)

Name	Salary
Anne	1700.00
Doug	950.00
Mark	2100.00

The program reads the following logical file records.

Table 4. Join logical file (JLF)

Name	Address	Telephone	Salary
Anne	120 1st St.	555–1111	1700.00
Doug	40 Pillsbury	555–5555	950.00
Mark	2 Lakeside Dr.	555–0000	2100.00

No record is returned for Tom because a record is not found for him in PF2 and PF3 and the JDFTVAL keyword is not specified. No record is returned for Sue because the primary file has no record for Sue.

 

Parent topic:

Setting up a join logical file