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.

For example, the SUPPLIERS file shows that several suppliers provide the same part. The user might want to know which supplier sets a price that is double, or greater than double, that of another. To transfer the necessary information to a workstation, specify:

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.

The final result is as follows:

     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