Physical and Logical Files, JOIN

Use this join-level keyword to identify which pair of files are joined by the join specification in which you specify this keyword.

The format of the keyword is:

JOIN(from-file to-file)

This keyword is valid only for join logical files.

You can use file names or relative file numbers to indicate which files are to be joined. You must specify a relative file number if the same file is specified more than once on the JFILE keyword.

If you specify file names, you must select files that you have specified only once on the JFILE keyword. On each JFILE keyword, the from file must occur before the to file.

If you specify numbers, they correspond to the files specified on the JFILE keyword. The following values are valid:

File: Valid values
From-file number: 1 through 255
To-file number: 2 through 256

The from-file number must always be less than the to-file number.

Special rules apply to the order in which you specify from and to files. See example 3 in this topic for details.

In a join logical file, each secondary file can be a to file only once.

Join specifications for physical and logical files

To describe a join specification, follow the instructions:

Specify J in position 17 immediately after the record level (before the first field name in positions 19 through 28). J in position 17 indicates the beginning of a join specification.
Specify the JOIN keyword. The JOIN keyword is optional when only two files are specified on the JFILE keyword. When more than two physical files are specified on the JFILE keyword, one JOIN keyword is required for each secondary file.
Specify the JFLD keyword at least once for each join specification.
The end of the join specification is indicated by another J in position 17 or by a field name specified in positions 19 through 28.

There must be one join specification for each secondary file specified on the JFILE keyword. Therefore, at least one join specification is required in a join logical file.

You can specify the JOIN keyword only once within a join specification.

Examples

The following examples show how to specify the JOIN keyword.

Example 1

|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8
00010A          R RECORD1                   JFILE(PFA PFB PFC)
00020A          J                           JOIN(PFA PFB)
00030A                                      JFLD(NAME1 NAME2)
00040A          J                           JOIN(PFA PFC)
00050A                                      JFLD(NAME1 NAME3)
00060A            NAME1
     A

In this example, PFA is joined to PFB and also to PFC.

Example 2

The following example shows how to specify JOIN using relative file numbers.

|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8
00010A          R RECORD1                   JFILE(PFA PFB PFC)
00020A          J                           JOIN(1 2)
00030A                                      JFLD(NAME1 NAME2)
00040A          J                           JOIN(1 3)
00050A                                      JFLD(NAME1 NAME3)
00060A            NAME1
     A

Example 2 is equivalent to example 1. PFA is the first physical file specified on the JFILE keyword and has relative file number 1. PFB and PFC are the second and third files specified on the JFILE keyword. PFB has relative file number 2, and PFC relative file number 3.

Example 3

The following example shows the order of associated physical files.

|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8
00010A          R J3                        JFILE(VENDORS PARTS PARTWARE +
00020A                                            WAREHOUSE
1
00030A          J                           JOIN(1   2)   2
00040A                                      JFLD(VNBR VNUM)
00050A          J                           JOIN(2   3)   3
00060A                                      JFLD(PNBR PNBR)
00070A          J                           JOIN(3   4)   3
00080A                                      JFLD(WNBR WNBR)
00090A            VNAME
00100A            VAD1
00110A            VAD2
00120A            PNBR                      JREF(2)
00130A            WNBR                      JREF(4)
00140A            BIN
00150A            QOH
     A

The join logical file in this example is based on four physical files. The VENDORS file, which is specified first on the JFILE keyword, is the primary file and has relative file number 1. The PARTS, PARTWARE, and WAREHOUSE files are secondary files. PARTS has relative file number 2, PARTWARE relative file number 3, and WAREHOUSE relative file number 4.

Notice the pattern of numbers specified on the JOIN keywords:

1

The first parameter value on the first JOIN keyword (the first from file) must be the primary file.

2

The second parameter values specified on the JOIN keywords (to files) must reflect the same order as the secondary files on the JFILE keyword. If file names are specified instead of relative file numbers, they should be specified in the following order:

J    JOIN(VENDORS PARTS)
J    JOIN(PARTS PARTWARE)
J    JOIN(PARTWARE WAREHOUSE)

3

On each JOIN keyword, the from and to files must be specified in ascending order.

Note: A file can be specified as a from file more than once. For example, the parameters on the JOIN keywords before might be specified as follows:

J    JOIN(1 2)
J    JOIN(2 3)
J    JOIN(2 4)

However, a file can be specified as a to file only once.

JOIN (Join) keyword—join logical files only

Join specifications for physical and logical files

Examples

Example 1

Example 2

Example 3