Missing Rows of Data in Cross-referenced Tables

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:


Top of page

x
The SET ALL Command

You can include short paths in a report with the FOCUS SET ALL command

SET ALL = {OFF|ON}

where:

OFF

Is the default value. In a join, omits host rows from the report if they lack a corresponding cross-referenced row.

ON

Includes all host rows in the report. (This is known as a left outer join.)

Note:


Top of page

x
Missing Rows in Unique Descendants

Reference:

In a unique join, the engine that handles the join controls the output:

The following topics discuss each of these settings.



x
Reference: How FOCUS Processes a Unique Join

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



Example: FOCUS Unique Join 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.



x
Reference: How the RDBMS Processes a Unique Join

The RDBMS processes the join under the following conditions:

The Adapter Optimizer discusses factors that determine whether optimization is enabled.


Top of page

x
Missing Rows in Non-unique Descendants

Reference:

In a non-unique join (JOIN…TO ALL) with missing cross-referenced rows, report results depend entirely on the SET ALL command because:



x
Reference: SET ALL=OFF With a Non-unique Join

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



Example: Non-Unique Join Processing With SET ALL = OFF

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


x
Reference: SET ALL=ON With a Non-unique Join

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.



Example: Non-Unique Join Processing With SET ALL = ON

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.



x
Reference: SET ALL=ON With Screening Conditions

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


x
Reference: Selective ALL. Prefix

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.


Top of page

x
Summary Chart

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