IBM SC41-5210-04 manual Specifying and selecting files for a Query for iSeries query

vA field name must be preceded by a 1- to 3-character file identifier if that field name is used in more than one file selected for the query.

vYou can specify up to 100 join tests.

vFor a matched-record join, if one or both fields in a join test are null, the records are not joined.

vThe data in both fields of a join test must be the same type (for example, both character), except that date, time, or timestamp fields can be compared to SBCS character, DBCS-either, or DBCS-open fields

that contain a correct representation of a date, time, or timestamp. Dates in character fields must be either in the date format of the query or in an SAA® format— the SAA format is recommended. Date, time, and timestamp fields are identified by an L, T, or Z, respectively, in the Dec column of the field list.

Note: When comparing a date, time, or timestamp field with an SBCS, DBCS-either, or DBCS-open character field, use a character field for which each value can be recognized as an SAA formatted date, time, or timestamp. If the character field contains a correct representation of a date, time, or timestamp but in other than an SAA format, you may get unexpected results. When you run a query that uses a non-SAA format for date, time, or timestamp values in character fields, and query has no setting for a non-SAA literal date value, use the CHGJOB command to make sure your job date format and separator match the format and separator in the character field values.

If some of the fields contain bracketed double-byte characters, you can use these DBCS fields for both fields, or you can use an SBCS character field for one field and a DBCS-either or DBCS-open field for the other field. Bracketed-DBCS fields are identified by a J, O, or E in the Dec column in the list of fields. Press F11 to see the Dec column if it is not currently shown.

A DBCS-graphic field can be compared only to another DBCS-graphic field. DBCS-graphic fields are identified by a G in the Dec column. Collating sequence is not applied to DBCS-graphic fields used in join comparisons.

Valid comparisons for join tests are:

–Numeric field with numeric field

–SBCS character field with:

-SBCS character

-Date

-Time

-Timestamp

-DBCS-either

-DBCS-open

–Date field with:

-Date

-SBCS character

-DBCS-either

-DBCS-open

–Time field with:

-Time

-SBCS character

-DBCS-either

-DBCS-open

–Timestamp field with:

-Timestamp

-SBCS character

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