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).
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.
Library/File (Member) | SUPPLIERS, INVENTORY |
JOIN BY | PARTNO = PARTNUM |
SELECT | PARTNO, DESCRIPTION, PRICE |
WHERE | SUPPNO = 51 |
ORDER BY | PARTNO |