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

Note: See “CCSID and join tests in Query for iSeries” on page 252 for information on how CCSIDs can
affect your join selections.
Specify How to Join Files
Type comparisons to show how file selections related, press Enter.
Tests: EQ, NE, LE, GE, LT, GT
Field Test Field
______________ _____ ______________
______________ _____ ______________
______________ _____ ______________
______________ _____ ______________
______________ _____ ______________
Bottom
________________________________________________________________________
Field Field
A.NBR B.NAME
A.NAME B.PHONE
A.ADDR B.SERIAL#
A.ZIP
B.NBR
Bottom
F3=Exit F5=Report F10=Process/previous F11=Display text
F12=Cancel F13=Layout F18=Files F24=More keys
For each test, you specify two fields to be tested and the test value to be used. Look for a field in one file
that contains the same (or similar) information that can be found in a field of the other file, such as a name
or identification number. Specify the names of the fields (including their file identifiers, if needed) on either
side of the test value.
For example, if you specify the EQ (equal) test value between two fields, the test result is true if both fields
contain the same value. (The EQ value is almost always used.)
Field Test Field
A.NAME EQ B.NAME
In this example, both fields are named
NAME, so the file identifiers (A and B) are included with the field
names.
Rules for joining files in a Query for iSeries query
Note: To indicate that you do not want to specify join tests, use the *ALL join. All records from one file are
joined to all records in the secondary file or files without any kind of selection.
The rules for join tests are:
v For two files to be selectively joined, they must have at least one field in common. (Note that having a
field in common does not mean that the field names are the same.) This is also true for logical
database files, even though they may be based on fields with different names in the physical file over
which the logical file is created.
v If you select option 2 (Matched records with primary file) or option 3 (Unmatched records with primary
file) on the Specify Type of Join display, the combined length of fields used in the join test must be less
than or equal to 2000 bytes. For DBCS fields, each DBCS character counts as two bytes. If fields
contain bracketed-DBCS data, the shift characters are counted in the length.
v You can only use the EQ test value to join any of your selected files to a join logical file.
v Fields in each join test must come from different files.
v You can use more than one test for a given pair of files. If you specify option 2 or 3 (both using a
primary file) in the Type of join prompt on the Specify Type of Join display, the test values must be the
same for all the tests used in that pair of files.
44 Query for iSeries Use V5R2