IBM ISERIES SC41-5210-04 - Manual (Page 60)

Example: Selecting matched records using a primary file in a Query for iSeries
query
Type a 2 if you want to include in the query output every record in the primary file and all the matching
records from all the other (secondary) files, whenever they exist. Every record in the primary file is
selected whether or not it has a match. (The primary file is always the one that was selected first in your
query definition.) Exception: if a field from the primary file used in a join test is null, the primary record is
not selected.
In this example, the RESIDENTS file is the primary file, so all of its records (numbered 1 through 6) are
included in the query report, assuming all of the records meet the selection tests on the Select Records
display. The
PHONELIST file is the only secondary file being used, and it supplies a telephone number for
each primary record that it matches; the NAME field is used as the comparison test field in both files. Note
also that record 4 is included twice in the report, because
Richard A Klein has two records, each with a
different telephone number, in the secondary file.
If a secondary file does not have a record that matches the join specifications of the primary file’s record,
blanks (for character fields), zeros (for numeric fields), or
January 1, 0001 (for date fields) are used as
data for that secondary file’s selected fields. If these fields are included as output fields in the query report,
the substituted characters or values are used in the report. In the example, the
PHONE field shows blanks
because it was coded as a character field. In the case where the fields are null-capable, the specified
default values are used as data for that secondary file’s selected fields. If a default value is not specified, a
null value is shown as a dash (-).
Note: If the secondary file was defined using DDS, values other than blanks zeros, and January 1, 0001
can be used when the DFT keyword defines default values for any of the fields. If the DFT keyword
specifies a default value for a field that is used in the query report, the default value is substituted
in the report when the secondary file does not have a matching record.
Example: Selecting unmatched primary file records in a Query for iSeries query
Type a 3 if you want to select, in the primary file, only records that lack matches in at least one secondary
file. That is, you want to select every primary record that does not have a matching record in all the
secondary files. For example, if four files were joined and only two of the three secondary files had
matching records, then a record containing the selected information in the primary and two matching
secondary files (and the default data, if any, from the unmatched secondary file) is included as a single
record in the query output.
This type of join is typically used to list records that are missing in one or more secondary files.
48 Query for iSeries Use V5R2