IBM SC41-5210-04 manual Query for iSeries Use V5R2

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.

48Query for iSeries Use V5R2