IBM SC41-5210-04 manual Examples of joining files in a Query for iSeries query

-DBCS-either

-DBCS-open

–DBCS-either field with:

-SBCS character

-Date

-Time

-Timestamp

-DBCS-either

-DBCS-open

-DBCS-only

–DBCS-open field with:

-SBCS Character

-Date

-Time

-Timestamp

-DBCS-either

-DBCS-open

-DBCS-only

–DBCS-only field with:

-DBCS-either

-DBCS-open

-DBCS-only

–DBCS-graphic field with:

-DBCS-graphic

–UCS2-graphic field with:

-UCS2-graphic

v Only the data fields specified in the join specifications for each file are used to join the files. For the purposes of joining the files, the other fields in the files are ignored.

vCharacter fields of different lengths can be joined.

vA fixed-length character field can be compared to a variable-length character field. Variable-length fields are identified by a V in the Dec column in the list of fields.

vIf you specified option 1, matched records, on the Specify Type of Join display, you can type *ALL in the first four spaces of the left field, but only in the first field name position, rather than specifying any join test. If *ALL is used, each record in the first file is joined to every record in the other files. For example, if a file of 2000 records is joined to a file of 3000 records using *ALL, the result is a joined file of 6000000 records. Using *ALL can significantly degrade the performance of your query. If join tests are not specified between each file, those files without a test are joined using the *ALL method.

Note: The fields you use to join the files do not have to be used in selecting the records or included in the query report. You can use those same fields in other ways in the query definition, such as for record selection or as part of the output of the query.

Examples of joining files in a Query for iSeries query

Consider an example where you want to join a master name and address file named RESIDENTS to a name and telephone number file name PHONELIST.

46Query for iSeries Use V5R2