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.
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.
- =
- Equal
- <> or ><
- Not equal
- >
- Greater than
- >=
- Greater than or equal to
- <
- Less than
- <=
- Less than or equal to
- 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.
- 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.
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.