IBM SC41-5210-04 manual Specifying and selecting files for a Query for iSeries query

In our example, the RESIDENTS file is still the primary file, so only its records that do not have a matching secondary record are included in the query report. The PHONELIST file has two such unmatched records; the residents identified in records 2 and 6 do not have a telephone number, so there are no records for them in the secondary file. (The NAME field is used again as the comparison test field in both files.)

As with the previous type of join, blanks (for character fields) or zeros (for numeric fields) are used as the data for a missing record in a secondary file’s selected fields. Or, if the DDS DFT keyword was used to define default values, those default values are used instead. (In our example, the PHONE field shows blanks, since it was coded as a character field and no DFT value was defined for the PHONE field.)

Sequencing secondary files for a primary join in a Query for iSeries query

The order in which you specify secondary files on the Specify File Selections display is important for some joins.

Specifically, the order of secondary files is important if all of the following are true:

vThe join type is 2 (primary matched) or 3 (primary unmatched). Both types have one primary file, followed by secondary files.

vThe query specifies three or more files in all.

vOne or more secondary files do not have join tests connecting them to the primary file.

If these points apply to your query, then follow the secondary file sequence rule:

Use join tests to connect each secondary file to a file listed above it on the Specify File Selections display.

For instance, when joining four files, use a join test to connect the second file to the first, and use another test to join the third file to the first or second file. The fourth file can be connected to any of the other files.

Example: Sequencing secondary files in a Query for iSeries query

To show how the secondary file sequence rule ensures that you get the desired results when you join more than two files, the following example uses three files in two ways, correctly and incorrectly. The first method, the correct method, produces five records when the files are joined. The incorrect method produces 15 records. The only difference between the methods is that the order of the second and third files is changed. For primary joins, Query joins files in the order indicated on the Specify File Selections display, not in the order indicated by the join tests.

Three files named CUSTOMER, PURCHASE, and ITEM are to be joined so that a report can be produced that lists each customer’s name and city, the item purchased (one item per line in the report), and a description of the item. (For example, if Monique Pottier bought three items, the report should have three lines for her, with each line listing her name, city, one item, and the item description.) If the customer did not make any purchases, the report should include the customer once in the list with blank item and description fields.

Chapter 4. Specifying and selecting files for a Query for iSeries query 49