Case 3: Secondary file has more than one match for a record in the primary file

 

This example join logical file contains a duplicate record in the primary file because it has more than one match in the secondary file.

Assume that a join logical file is specified as in Example 1: Basic concepts of joining two physical files, and there are four records in PF1 and five records in PF2, as shown in the following two tables.

Table 1. Physical file 1 (PF1)

Employee number	Name
235	Anne
440	Doug
500	Mark
729	Sue

Table 2. Physical file 2 (PF2)

Employee number	Salary
235	1700.00
235	1500.00
440	950.50
500	2100.00
729	1400.90

In PF2, the record for 235 is duplicated.

The program gets five records.

Table 3. Join logical file (JLF)

Employee number	Name	Salary
235	Anne	1700.00
235	Anne	1500.00
440	Doug	950.50
500	Mark	0000.00
729	Sue	1400.90

In the join records, the record for 235 is duplicated. The order of the records received for the duplicated record is unpredictable unless the JDUPSEQ keyword is used.

 

Parent topic:

Example 1: Basic concepts of joining two physical files

 

Related reference

Example 3: Reading duplicate records in the secondary file