Receiving Records Joined from Several iSeries, eServer i5, or System i5 Files

Two iSeries, eServer™ i5, or System i5® files, INVENTORY and SUPPLIERS, are assumed. Note that both files contain records including part number fields. The INVENTORY file contains inventory information about individual parts. The SUPPLIERS file contains information about purchasing and ordering.

You might want to transfer information on part numbers, part names, and the prices of the parts to be ordered from supplier 51. The desired fields are PARTNO (SUPPLIERS file), DESCRIPTION (INVENTORY file), and PRICE (SUPPLIERS file).

By comparing the data in the INVENTORY file and the SUPPLIERS file, the user can determine that supplier 51 provides part numbers 221 and 231, called BOLT and NUT, respectively, and that their prices are 30 cents and 10 cents, respectively. The following table summarizes this information:

     Field:   PARTNO   DESCRIPTION   PRICE
              ------   -----------   -----
  Record 1:    221     BOLT            .30
         2:    231     NUT             .10

The same results are available by joining the data in these two files by using the iSeries→PC Transfer function. To do this, specify both files (INVENTORY and SUPPLIERS) in the FROM item. For SELECT, specify which fields are to be transferred (PARTNO, DESCRIPTION, and PRICE). For WHERE, specify which records are to be transferred (records for which SUPPNO = 51).

Respecify the relationship between the two files in JOIN BY. From these results, the user can determine, by checking the SUPPLIERS file, that part number 221 is delivered from supplier 51 at a cost of 30 cents. In addition, to determine the part name, the user must check the INVENTORY file for part number 221 and its product name. In other words, the user observes that data is joined from the records in both the SUPPLIERS file and the INVENTORY file and that those records have the same part number. Therefore, to link the two records in these files, the records must have the same part number.

In short, to obtain this information, specify:

Library/File (Member)	SUPPLIERS, INVENTORY
JOIN BY	PARTNO = PARTNUM
SELECT	PARTNO, DESCRIPTION, PRICE
WHERE	SUPPNO = 51
ORDER BY	PARTNO