Filter
Top Products
every record in file C. Because there are three records in A and five records in C, the result is 15 records.
The join tests are not used in this step. The result of step 1 this time is a working file called AC.
Step 2: Join file AC to file B.
Query joins each record in file AC to every record in PURCHASE file B for which A.NAME equals B.NAME and
B.ITEM equals C.ITEM. If a record in AC (such as Martinez) has no match in B, Query joins it to a default
record for file B, which is blank. This completes the join operation.
The incorrect method produces 15 records, which is 10 too many. Each customer has five records, one for
each item description, even if the customer made no purchases. Note that if the ITEM file has 1000 records
instead of five, the correct method still selects five records, but the incorrect method selects 3000 records.
Also, the ITEM and DESCRIPT values for Martinez are not blank as they should be.
In summary, this problem does not affect queries with join type 1 (matched), queries with just two files, or
queries that use the primary file in each join test. In this example, the logical order to specify files is
CUSTOMER, PURCHASE, and ITEM, with PURCHASE in the middle because it is the connection between the
CUSTOMER and ITEM files. This logical order is also the correct order.
Displaying all join tests in a Query for iSeries query
When you press the Enter key on the Display File Selections display (see “Displaying all files selected on
the Query for iSeries Display File Selections display” on page 41), the Display Join Tests display is shown.
The information on this display is for your information only; none of the entries can be changed here. (The
following display shows some sample information.)
Display Join Tests
Type of join......: Matched Records
Field Test Field
A.NAME EQ B.NAME
Bottom
Press Enter to continue.
F12=Cancel
The Display Join Tests display shows:
v The type of join being used to join all the files in the query. One of three join types can be specified:
– Matched records
– Matched records with primary file
– Unmatched records with primary file
The primary file, used in the last two types, is the first file listed on the Display File Selections display.
For more information, press F11 (Search index), type joining files as the index search words, then press
the Enter key.
v The join tests being used to join the files.
Chapter 4. Specifying and selecting files for a Query for iSeries query 53