IBM SC41-5210-04 manual Displaying all join tests in a Query for iSeries query, Name

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:

vThe 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.

vThe join tests being used to join the files.

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