JOIN BY

When several files have been specified in FROM, specify JOIN BY. When only one file has been specified in FROM, JOIN BY does not appear.

JOIN BY specifies how to link or join the records of the files specified in FROM. Each file specified in FROM must be joined with at least one other file that has been specified in FROM.

Use JOIN BY to specify one or more join conditions. The join conditions indicate the similarity of two files. Therefore, they indicate which records of one file are joined with those of another.

The join conditions are as follows:

field-name = field-name

Field name is the name of the field defined in the record format specified in FROM. The join conditions require two field names, one for each file to be joined.

Field names must be delimited by one of these:

=: Equal
<> or ><: Not equal
>: Greater than
>=: Greater than or equal to
<: Less than
<=: Less than or equal to

When specifying fields in JOIN BY, observe the following rules.

Join a numeric field to another numeric field. The field lengths and types do not have to be identical.
Join a character field to another character field. The lengths do not have to be identical.

The field name to be specified might have been defined in the files specified in FROM. When such a field name is used in the following items, prefix the field name with the file qualifier:

JOIN BY
GROUP BY
SELECT
WHERE
HAVING
ORDER BY

The file qualifier is the character T (uppercase or lowercase) followed by a one- or two-digit number. Use T1 for fields defined with the first record format, T2 for fields defined with the second record format, and so on. Delimit the file qualifier and field name with a period (.). See Receiving Records Using File Qualifiers for details of the file qualifiers.

If the field name of the file specified in FROM cannot be found, select Browse when the cursor is on the JOIN BY input area. Then, a list of file qualifiers and field names of the files appears.

To join three or more files, or to join two files based on two or more common fields, two or more link conditions must be used. To specify several join conditions, join the conditions with AND. For example:

T1.EMPNO = T2.EMPNO AND T2.EMPNO = T3.EMPNO

In this case, records having the same value as EMPNO are joined between the first and second files specified in FROM. Then, such records are joined between the second and third files specified in FROM.

Up to 32 join conditions can be specified.

After JOIN BY is specified, each of SELECT, WHERE, and ORDER BY can be completed, by following the procedure described earlier in this chapter. To browse a field name that has been defined in several files, prefix the field name with a file qualifier.