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

v A 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.
v You can specify up to 100 join tests.
v For a matched-record join, if one or both fields in a join test are null, the records are not joined.
v The 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