IBM ISERIES SC41-5210-04, User Manual

Page: 62 / 294

Correct method:
On the Specify File Selections display, type the
CUSTOMER
file first, the
PURCHASE
file second, and the
ITEM
file third. Specify the file IDs
A
,
B
, and
C
, respectively. The join type is 2 (Matched records with primary
file). The join tests are:
A.NAME EQ B.NAME
B.ITEM EQ C.ITEM
Query performs the file join in two steps:
Step 1: Join the first two files,
A
and
B
.
Query joins the files in the order listed, starting with file
A
(
CUSTOMER
), and file
B
(
PURCHASE
). Query joins
each record in file
A
to any record in file
B
for which the
NAME
field in
A
is equal to the
NAME
field in
B
.
Because this query is join type 2 (primary matched), every record in the primary file
A
is included in the
join. If a record in file
A
(such as Martinez) has no match in
B
, Query joins it to a default record for file
B
,
which has blank values for fields. The result of step 1 in our example is a file called
AB
. (Note that
AB
is a
working file used to build the joined file that you want in your report. You cannot see file
AB
.)
Step 2: Join file
AB
to file
C
.
Query joins each record in file
AB
to every record in
ITEM
file
C
for which
B.ITEM
equals
C.ITEM
. If a record
in
AB
(such as Martinez) has no match in file
C
, Query joins it to a default record for file
C
, which is also
blank. This completes the join operation. Each record in primary file
A
is represented one or more times in
the five records.
50
Query for iSeries Use V5R2