IBM SC41-5210-04 manual Joining files in Query for iSeries, Using *ALL in Query for iSeries

Joining files in Query for iSeries

For joining files (type of join):

vType 1 join is the same as for Query/36 (on System/36).

vType 2 join selects all the records in a primary file and records in the secondary files that match the primary records. (It selects the matches.)

vType 3 join selects only the records in a primary file that have no matching records in the secondary files. (It selects the exceptions.)

For a complete description and examples of each type of join, refer to “Joining files in a Query for iSeries query” on page 42.

Using *ALL in Query for iSeries

You should be wary of using an *ALL join because it can return a large number of records. Refer to

“Joining files in a Query for iSeries query” on page 42 in this guide for more information. You can use *ALL for your join if you are creating a database file with field extension using a one-record pad file, or there are few records in any of the files being joined and you want all of the formats combined.

Using fields other than sort fields for report breaks in Query for iSeries

The fields you use for sorting may not be meaningful in a report, so you may want to use fields other than sort fields for break fields. For example, a field containing the customer name portion of a mailing label may be used as the break field, while an arbitrarily assigned (also unique) customer ID is used for sorting. Since customer name is a break field, it can be inserted in break text or placed in a summary-only database file.

Result field length and decimal positions in Query for iSeries

Query determines the presentation length and number of decimal positions for result fields when it creates them in the report. These values are satisfactory for most users. The following sections suggest when, and how, the user should specify length and decimal positions. See “Length and decimal positions in Query for iSeries reports” on page 120 and “Length and decimal positions in Query for iSeries” on page 85 for further information.

Tips for dealing with presentation length and decimal positions in Query for iSeries

In certain situations, the length that Query determines for the result field is larger than necessary. For example, for result field RESULT10:

Result Field	---	Values and	Operands	---	Layout
RESULT10	9	+ 9 + 9	+ 9 +	9	999999

Query assigns length 6 to result field RESULT10. Since the result in RESULT10 is 45, only a length of 2 is needed. You could specify a 2 in Len column and a 0 in the Dec column on the Define Result Fields display for this result field.

Similarly, for result field RESULT11:

Result Field	--- Values	and Operands ---	Layout
RESULT11	N1 + N2	+ N3 + N5 + N5	99999

If fields N1 through N5 each have a length of 1, Query assigns a length of 5 to RESULT11, but a length of 2 is long enough.

188Query for iSeries Use V5R2