IBM SC41-5210-04 manual Rules for joining files in a Query for iSeries query, NBR Name Phone Addr

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.

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:

vFor 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.

vIf 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.

vYou can only use the EQ test value to join any of your selected files to a join logical file.

vFields in each join test must come from different files.

v

44Query for iSeries Use V5R2