Receiving with Records in a Same File Joined
Records in the same file can be joined. In other words, a file can be repeatedly specified in FROM. For instance, data in certain records can be compared using this function.
Library/File (Member) | SUPPLIERS, SUPPLIERS |
JOIN BY | T1.PARTNO = T2.PARTNO |
SELECT | T1.PARTNO, T1.SUPPNO, T1.PRICE, T2.SUPPNO, T2.PRICE |
WHERE | T1.PRICE > 2 * T2.PRICE |
ORDER BY | T1.PARTNO |
The same file has been specified in FROM twice. JOIN BY specifies that records having the same part number are joined. This creates a joined record containing information about two suppliers of a single part. The user can spot those records for which the price is double, or greater than double, that of another supplier.
Records in the SUPPLIERS file are compared, one by one, with all the records (including itself) in the SUPPLIERS file. When the same part number is found, the two corresponding records are linked. This processing is performed for each record in the SUPPLIERS file.
For each record, the first supplier's price is compared with the second supplier's price. When the first supplier's price is double, or greater than double, that of the second, only the record containing the first supplier price is kept.
Field: T1.PARTNO T1.SUPPNO T1.PRICE T2.SUPPNO T2.PRICE
--------- --------- -------- --------- --------
Record 1: 221 51 .30 54 .10
2: 231 51 .10 54 .04
3: 241 53 .08 54 .02
4: 241 61 .05 54 .02