Filter
Top Products
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:
v The join type is 2 (primary matched) or 3 (primary unmatched). Both types have one primary file,
followed by secondary files.
v The query specifies three or more files in all.
v One 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