In this section: |
This topic describes factors that affect report results when a host row has no corresponding cross-referenced row. The discussion applies to both dynamic and embedded joins.
Normally, when a row from the host table or view is retrieved, a corresponding row from the cross-referenced table can also be retrieved. If a host row lacks a corresponding cross-referenced row, the retrieval path is called a short path. When there are short paths, the processing of the host row and the report results depend on:
Note: Even if you set OPTIMIZATION ON, the adapter may disable it. Use the SQLAGGR trace component to determine whether optimization is enabled for a particular request (Tracing Adapter Processing describes trace facilities).
You can include short paths in a report with the FOCUS SET ALL command
SET ALL = {OFF|ON}
where:
Is the default value. In a join, omits host rows from the report if they lack a corresponding cross-referenced row.
Includes all host rows in the report. (This is known as a left outer join.)
Note:
Reference: |
In a unique join, the engine that handles the join controls the output:
In this case the SQLJOIN OUTER OFF setting requires that FOCUS process the left outer join even though other optimization features remain in effect.
Note: When FOCUS handles join processing, if you describe a join as unique when the cross-referenced table actually contains more than one matching record for a row in the host table, FOCUS displays only the first matching cross-referenced row on the report. Do not specify a unique join to FOCUS when the data structure is non-unique.
The following topics discuss each of these settings.
FOCUS handles the join when either of the following conditions is true:
FOCUS substitutes default values for any missing cross-referenced data (because it does not consider them to be missing in a unique join). FOCUS recognizes that you have defined the join to be unique and:
The Adapter Optimizer discusses factors that determine whether optimization is disabled. To find out if and why the adapter disabled OPTIMIZATION for a report request, use the SQLAGGR trace component (see Tracing Adapter Processing).
In the following example, a unique join connects the EMPINFO table, containing employee information, to the FUNDTRAN table, containing direct deposit account information for the employees. (File Descriptions and Tables provides the Master and Access Files.)
JOIN EMP_ID IN EMPINFO TAG FILE1 TO WHO IN FUNDTRAN TAG FILE2 AS J1 SQL DB2 SET OPTIMIZATION OFF TABLE FILE EMPINFO PRINT BANK_NAME BANK_ACCT BY DEPARTMENT BY EMP_ID END NUMBER OF RECORDS IN TABLE= 14 LINES= 14
Since the join is unique and the SET OPTIMIZATION OFF command disables optimization, FOCUS displays one row per employee on the report. If there is no data for the cross-referenced table (FUNDTRAN), FOCUS appropriately substitutes zeros or blanks for its fields.
Two new employees, John Royce (333121200, no department) and Donna Lee (455670000, MIS) have no bank accounts. Six other employees also lack bank accounts:
DEPARTMENT EMP_ID BANK_NAME BANK_ACCT ---------- ------ --------- --------- . 333121200 MIS 112847612 117593129 STATE 40950036 219984371 326179357 ASSOCIATED 122850108 455670000 543729165 818692173 BANK ASSOCIATION 163800144 PRODUCTION 071382660 119265415 119329144 BEST BANK 160633 123764317 ASSOCIATED 819000702 126724188 451123478 ASSOCIATED 136500120
Note: Employee 333121200 has not yet been assigned a department. Its null value is indicated by the NODATA symbol. However, BANK_NAME and BANK_ACCT are not considered missing because the join is unique. Note that the bank name and bank account values, although integers, display as blanks instead of zeros because of the S display option in their USAGE formats.
The RDBMS processes the join under the following conditions:
Since the RDBMS does not recognize the concept of a unique join, it performs an inner join just as it does if the join is non-unique. See Missing Rows in Non-unique Descendants for a complete discussion.
If SQLJOIN OUTER is ON, the RDBMS performs a left outer join just as it does if the join is non-unique. See Missing Rows in Non-unique Descendants for a complete discussion.
(If SQLJOIN OUTER is OFF, FOCUS processes the join. It substitutes default values for short path columns and displays only one matching record as described in How FOCUS Processes a Unique Join.)
The Adapter Optimizer discusses factors that determine whether optimization is enabled.
Reference: |
In a non-unique join (JOIN…TO ALL) with missing cross-referenced rows, report results depend entirely on the SET ALL command because:
With SET ALL=OFF, optimization may be enabled or disabled. In both cases, however, the report results are the same, an inner join. Host rows that lack corresponding cross-referenced rows are not included in the report. Multiple matching rows are included (even if there is only one).
For each of the following examples, the same non-unique join is in effect. It connects the EMPINFO table, containing employee information, to the DEDUCT table, containing salary deduction information.
The examples also execute the same report request. OPTIMIZATION is set ON and only the SET ALL command changes.
Two employees, John Royce (333121200, no department) and Donna Lee (455670000, MIS) have not been paid yet. Therefore, they have no deductions.
When SET ALL is OFF, a host row that lacks a corresponding cross-referenced row is rejected (regardless of whether FOCUS or the RDBMS processes the join):
JOIN CLEAR J1 JOIN EMP_ID IN EMPINFO TAG FILE1 TO ALL DEDEID IN DEDUCT TAG FILE2 AS J2 SET ALL=OFF TABLE FILE EMPINFO PRINT DED_CODE DED_AMT BY DEPARTMENT BY EMP_ID BY DEDDATE END NUMBER OF RECORDS IN TABLE= 448 LINES= 448
The report displays multiple deduction records in the cross-referenced table for each of 12 long-time employees in the host table. John Royce (333121200, no department) and Donna Lee (455670000, MIS department) are not listed in the report, because their corresponding cross-referenced rows do not exist in the DEDUCT table:
DEPARTMENT EMP_ID DEDDATE DED_CODE DED_AMT ---------- ------ ------- -------- ------- MIS 112847612 82/01/29 CITY $1.43 FED $121.55 FICA $100.10 HLTH $22.75 LIFE $13.65 SAVE $54.60 STAT $20.02 82/02/26 CITY $1.43 FED $121.55 FICA $100.10 HLTH $22.75 LIFE $13.65 SAVE $54.60 STAT $20.02 . . .
With a non-unique join, SET ALL=ON produces a left outer join regardless of whether FOCUS or the RDBMS handles the join:
In both cases the report results are the same.
If there are no cross-referenced rows for a host row, the cross-referenced columns display the NODATA value.
The following example executes the same report request as in Non-Unique Join Processing With SET ALL = OFF, except that SET ALL is ON:
SET ALL = ON TABLE FILE EMPINFO PRINT DED_CODE DED_AMT BY DEPARTMENT BY EMP_ID BY DEDDATE END NUMBER OF RECORDS IN TABLE= 450 LINES= 450
John Royce (333121200) is now on Page 1 and Donna Lee (455670000) on Page 8. The department column for John Royce (333121200) displays the NODATA value, since the field has MISSING=ON:
PAGE 1 DEPARTMENT EMP_ID DEDDATE DED_CODE DED_AMT ---------- ------ ------- -------- ------- . 333121200 . . . MIS 112847612 82/01/29 CITY $1.43 FED $121.55 FICA $100.10 HLTH $22.75 LIFE $13.65 . . .
Donna Lee (455670000) works for MIS:
PAGE 8 DEPARTMENT EMP_ID DEDDATE DED_CODE DED_AMT ---------- ------ ------- -------- ------- MIS 326179357 82/07/30 STAT $88.93 82/08/31 CITY $6.35 FED $539.96 FICA $444.67 HLTH $45.37 LIFE $27.22 SAVE $108.90 STAT $88.93 455670000 . . . 543729165 82/04/30 CITY $.72 . . .
Note: The report that results from passing a request to the RDBMS with SET ALL=ON may be sorted in a slightly different order from the report produced if FOCUS processes the join. However, both results are equally correct.
When SET ALL=ON, screening conditions affect report results for a non-unique join. If a screening test specifies a field from the cross-referenced structure, host rows whose cross-referenced rows were screened out are not represented, regardless of the SET ALL command.
The following example includes a WHERE test to screen for health deduction records:
JOIN EMP_ID IN EMPINFO TAG F1 TO ALL DEDEID IN DEDUCT TAG F2 AS JOIN1 SET ALL=ON TABLE FILE EMPINFO SUM DED_AMT BY DEPARTMENT BY LAST_NAME BY FIRST_NAME BY DED_CODE WHERE DED_CODE EQ 'HLTH' END
Royce and Lee are omitted as the result of the WHERE test, because they have no deduction code records:
DEPARTMENT LAST_NAME FIRST_NAME DED_CODE DED_AMT ---------- --------- ---------- -------- ------- MIS BLACKWOOD ROSEMARIE HLTH $226.87 CROSS BARBARA HLTH $330.21 JONES DIANE HLTH $121.16 MCCOY JOHN HLTH $16.50 SMITH MARY HLTH $181.99 PRODUCTION BANNING JOHN HLTH $41.25 IRVING JOAN HLTH $427.35 MCKNIGHT ROGER HLTH $122.43 ROMANS ANTHONY HLTH $105.60 STEVENS ALFRED HLTH $69.44
Even if SET ALL=OFF, you can apply the effect of the ON setting to specific tables. To do this, add the ALL. prefix to one of the host fields in the request. The ALL. prefix causes FOCUS to process host rows even if they have missing cross-referenced rows. Like SET ALL=ON, the report results depend on whether screening tests exist for the cross-referenced fields.
For example, when the ALL. prefix is applied to the field DEPARTMENT, the report results are the same as for SET ALL=ON. (The report is displayed in Non-Unique Join Processing With SET ALL = ON).
SET ALL=OFF TABLE FILE EMPINFO PRINT DED_CODE DED_AMT BY ALL.DEPARTMENT BY EMP_ID BY DEDDATE END
Note: The ALL. prefix is only effective when the SET ALL value is OFF. SET ALL=ON overrides the ALL. prefix.
This summary chart lists possible report results for dynamic joins and multi-table Master Files (embedded joins):
Join Type |
RDBMS Behavior (Optimization Enabled) |
FOCUS Behavior (Optimization Disabled) | ||
---|---|---|---|---|
SET ALL = |
SET ALL = | |||
ON (SQLJOIN OUTER ON) |
OFF |
ON |
OFF | |
NON-UNIQUE
Dynamic (JOIN...TO ALL)or Embedded (SEGTYPE=S0
or KL)
|
Outer Join |
Inner Join |
Outer join (Also applies if Optimization Enabled and SQLJOIN OUTER OFF) |
Inner join |
Short paths |
Appear with NODATA (.) value for fields of all missing segments |
Do not appear |
Appear with NODATA (.) value for fields of all missing segments |
Do not appear |
More than one matching row |
All matching rows appear |
All matching rows appear |
All matching rows appear |
All matching rows appear |
UNIQUE
Dynamic (JOIN...TO)or Embedded (SEGTYPE=U
or KLU)
|
Outer join |
Inner Join |
FOCUS Unique (Also applies if Optimization Enabled and SQLJOIN OUTER OFF) |
FOCUS Unique |
Short paths |
Appear with NODATA (.) value for fields of all missing segments |
Do not appear |
Appear with blank or zero for missing values |
Appear with blank or zero for missing values |
More than one matching row |
All matching rows appear |
All matching rows appear |
Only first matching row appears |
Only first matching row appears |
Note:
Information Builders |