Handling a Missing Segment Instance

In this section:

In multi-segment data sources, when an instance in a parent segment does not have descendant instances, the nonexistent descendant instances are called missing instances.

When you write a request from a data source that has missing segment instances, the missing instances affect the report. For example, if the request names fields in a segment and its descendants, the report omits parent segment instances that have no descendants. It makes no difference whether fields are display fields or sort fields.

When an instance is missing descendants in a child segment, the instance, its parent, the parent of its parent, and so on up to the root segment, is called a short path. Unique segments are never considered to be missing.

For example, consider the following subset of the EMPLOYEE data source.

Suppose some employees are paid by an outside agency. None of these employees have a company salary history. Instances referring to these employees in the salary history segment are missing.

Nonexistent descendant instances affect whether parent segment instances are included in report results. The SET ALL parameter and the ALL. prefix enable you to include parent segment data in reports.

For illustrations of how missing segment instances impact reporting, see Reporting Against Segments Without Descendant Instances and Reporting Against Segments With Descendant Instances.

Top of page
Example: Reporting Against Segments Without Descendant Instances

The following request displays the salary histories for each employee. 

TABLE FILE EMPLOYEE
PRINT SALARY
BY LAST_NAME BY FIRST_NAME
BY DAT_INC
END

However, two employees, Davis and Gardner, are omitted from the following report because the LAST_NAME and FIRST_NAME fields belong to the root segment, and the DAT_INC and SALARY fields belong to the descendant salary history segment. Since Davis and Gardner have no descendant instances in the salary history segment, they are omitted from the report.

The output is:

LAST_NAME        FIRST_NAME   DAT_INC           SALARY
---------        ----------   -------           ------
BANNING          JOHN        82/08/01       $29,700.00
BLACKWOOD        ROSEMARIE   82/04/01       $21,780.00
CROSS            BARBARA     81/11/02       $25,775.00
                             82/04/09       $27,062.00
GREENSPAN        MARY        82/04/01        $8,650.00
                             82/06/11        $9,000.00
IRVING           JOAN        82/01/04       $24,420.00
                             82/05/14       $26,862.00
JONES            DIANE       82/05/01       $17,750.00
                             82/06/01       $18,480.00
MCCOY            JOHN        82/01/01       $18,480.00
MCKNIGHT         ROGER       82/02/02       $15,000.00
                             82/05/14       $16,100.00
ROMANS           ANTHONY     82/07/01       $21,120.00
SMITH            MARY        82/01/01       $13,200.00
                 RICHARD     82/01/04        $9,050.00
                             82/05/14        $9,500.00
STEVENS          ALFRED      81/01/01       $10,000.00
                             82/01/01       $11,000.00
Top of page
Example: Reporting Against Segments With Descendant Instances

The following request displays the average salary and second address line of each employee. The data source contains Davis' address, but not Gardner's. 

TABLE FILE EMPLOYEE
SUM AVE.SALARY
AND ADDRESS_LN2
BY LAST_NAME BY FIRST_NAME
END

This report displays Davis' name even though Davis has no salary history, because Davis has an instance in the descendant address segment. The report omits Gardner entirely, because Gardner has neither a salary history nor an address.

The output is: 

LAST_NAME        FIRST_NAME           SALARY  ADDRESS_LN2        
---------        ----------           ------  -----------        
BANNING          JOHN             $29,700.00  APT 4C             
BLACKWOOD        ROSEMARIE        $21,780.00  3704 FARRAGUT RD.  
CROSS            BARBARA          $26,418.50  147-15 NORTHERN BLD
DAVIS            ELIZABETH                 .  2530 AMSTERDAM AVE.
GREENSPAN        MARY              $8,825.00  13 LINDEN AVE.     
IRVING           JOAN             $25,641.00  123 E 32 ST.       
JONES            DIANE            $18,115.00  235 MURRAY HIL PKWY
MCCOY            JOHN             $18,480.00  2 PENN PLAZA       
MCKNIGHT         ROGER            $15,550.00  117 HARRISON AVE.  
ROMANS           ANTHONY          $21,120.00  271 PRESIDENT ST.  
SMITH            MARY             $13,200.00  2 PENN PLAZA       
                 RICHARD           $9,275.00  136 E 161 ST.      
STEVENS          ALFRED           $10,500.00  2 PENN PLAZA
Top of page
x
Including Missing Instances in Reports With the ALL. Prefix

If a request excludes parent segment instances that lack descendants, you can include the parent instances by attaching the ALL. prefix to one of the fields in the parent segment.

Note that if the request contains WHERE or IF criteria that screen fields in segments that have missing instances, the report omits parent instances even when you use the ALL. prefix. To include these instances, use the SET ALL=PASS command described in Including Missing Instances in Reports With the SET ALL Parameter.

Example: Including Missing Segment Instances With the ALL. Prefix

The following request displays the salary history of each employee. Although employees Elizabeth Davis and David Gardner have no salary histories, they are included in the report. 

TABLE FILE EMPLOYEE
PRINT SALARY
BY ALL.LAST_NAME BY FIRST_NAME
BY DAT_INC
END

The output is:

LAST_NAME        FIRST_NAME   DAT_INC           SALARY
---------        ----------   -------           ------
BANNING          JOHN        82/08/01       $29,700.00
BLACKWOOD        ROSEMARIE   82/04/01       $21,780.00
CROSS            BARBARA     81/11/02       $25,775.00
                             82/04/09       $27,062.00
DAVIS            ELIZABETH          .                .
GARDNER          DAVID              .                .
GREENSPAN        MARY        82/04/01        $8,650.00
                             82/06/11        $9,000.00
IRVING           JOAN        82/01/04       $24,420.00
                             82/05/14       $26,862.00
JONES            DIANE       82/05/01       $17,750.00
                             82/06/01       $18,480.00
MCCOY            JOHN        82/01/01       $18,480.00
MCKNIGHT         ROGER       82/02/02       $15,000.00
                             82/05/14       $16,100.00
ROMANS           ANTHONY     82/07/01       $21,120.00
SMITH            MARY        82/01/01       $13,200.00
                 RICHARD     82/01/04        $9,050.00
                             82/05/14        $9,500.00
STEVENS          ALFRED      81/01/01       $10,000.00
                             82/01/01       $11,000.00
Top of page
x
Including Missing Instances in Reports With the SET ALL Parameter

How to:

Reference:

You can control how parent instances with missing descendants are processed by issuing the SET ALL command before executing the request. In a join, issuing the SET ALL = ON command controls left outer join processing.

Note: A request with WHERE or IF criteria, which screen fields in a segment that has missing instances, omits instances in the parent segment even if you use the SET ALL=ON command. To include these instances in a report , use the SET ALL=PASS command.

In FOCUS, the command SET ALL = ON or JOIN LEFT_OUTER specifies a left outer join. With a left outer join, all records from the host file display on the report output. If a cross-referenced segment instance does not exist for a host segment instance, the report output displays missing values for the fields from the cross-referenced segment.

If there is a screening condition on the dependent segment, those dependent segment instances that do not satisfy the screening condition are omitted from the report output, and so are their corresponding host segment instances. With missing segment instances, tests for missing values fail because the fields in the segment have not been assigned missing values.

When a relational engine performs a left outer join, it processes host records with missing cross-referenced segment instances slightly differently from the way FOCUS processes those records when both of the following conditions apply:

When these two conditions are true, FOCUS omits the host record from the report output, while relational engines supply null values for the fields from the dependent segment and then apply the screening condition. If the missing values pass the screening condition, the entire record is retained on the report output. This type of processing is useful for finding or counting all host records that do not have matching records in the cross-referenced file or for creating a DEFINE-based join from the cross-referenced segment with the missing instance to another dependent segment.

If you want FOCUS to assign null values to the fields in a missing segment instance when a left outer join is in effect, you can issue the command SET SHORTPATH=SQL.
x
Syntax: How to Include a Parent Instance With Missing Descendants
SET ALL= {OFF|ON|PASS}

where:

OFF

Omits parent instances that are missing descendants from the report. OFF is the default value.

ON

Includes parent instances that are missing descendants in the report. However, if a test on a missing segment fails, this causes the parent to be omitted from the output. It is comparable to the ALL. prefix.

PASS

Includes parent instances that are missing descendants, even if WHERE or IF criteria exist to screen fields in the descendant segments that are missing instances (that is, a test on a missing segment passes).

Example: Including Missing Segment Instances With SET ALL

The following request displays all employees, regardless of whether they have taken a course or not since the ALL=PASS command is set.

If the ALL=ON command had been used, employees that had not taken courses would have been omitted because of the WHERE criteria.

JOIN EMPDATA.PIN IN EMPDATA TO ALL TRAINING.PIN IN TRAINING AS JOIN1
SET ALL = PASS
TABLE FILE EMPDATA
PRINT LASTNAME AND FIRSTNAME AND COURSECODE AND EXPENSES 
BY PIN
WHERE EXPENSES GT 3000
END

The output is:

PIN        LASTNAME         FIRSTNAME   COURSECODE   EXPENSES 
---        --------         ---------   ----------   -------- 
000000020  BELLA            MICHAEL     .                   . 
000000040  ADAMS            RUTH        EDP750       3,400.00 
000000050  ADDAMS           PETER       UMI720       3,300.00 
000000060  PATEL            DORINA      .                   . 
000000070  SANCHEZ          EVELYN      .                   . 
000000080  SO               PAMELA      BIT420       3,350.00 
           SO               PAMELA      EDP690       3,200.00 
000000090  PULASKI          MARIANNE    .                   . 
000000100  ANDERSON         TIM         NAMA930      3,100.00 
000000130  CVEK             MARCUS      .                   . 
000000140  WHITE            VERONICA    BIT420       3,600.00 
000000150  WHITE            KARL        UNI780       3,400.00 
000000170  MORAN            WILLIAM     .                   . 
000000190  MEDINA           MARK        EDP690       3,150.00 
000000220  LEWIS            CASSANDRA   .                   . 
000000230  NOZAWA           JIM         .                   . 
000000300  SOPENA           BEN         .                   . 
000000340  GOTLIEB          CHRIS       EDP750       3,450.00
           GOTLIEB          CHRIS       SSI670       3,300.00
000000350  FERNSTEIN        ERWIN       UMI720       3,350.00
000000380  ELLNER           DAVID       UNI780       3,350.00
000000390  GRAFF            ELAINE      .                   .
000000400  LOPEZ            ANNE        .                   .
000000410  CONTI            MARSHALL    EDP690       3,100.00
x
Syntax: How to Control Short Path Processing In a Left Outer Join
SET SHORTPATH = {FOCUS|SQL}

where:

FOCUS

Omits a host segment from the report output when it has no corresponding cross-referenced segment and the report has a screening condition on the cross-referenced segment.

SQL

Supplies missing values for the fields in a missing cross-referenced segment in an outer join. Applies screening conditions against this record and retains the record on the report output if it passes the screening test. Note: There must be an outer join in effect, either as a result of the SET ALL=ON command or a JOIN LEFT_OUTER command (either inside or outside of the Master File).

x
Reference: Usage Notes for SET SHORTPATH = SQL

A FOCUS data source is supported as the host file in a join used with SET SHORTPATH = SQL, but not as the cross-referenced file.

Example: Controlling Outer Join Processing

The following procedure creates two Oracle tables, ORAEMP and ORAEDUC, that will be used in a join.

TABLE FILE EMPLOYEE
SUM LAST_NAME FIRST_NAME CURR_SAL CURR_JOBCODE DEPARTMENT
BY EMP_ID
ON TABLE HOLD AS ORAEMP FORMAT SQLORA
END
-RUN
TABLE FILE EDUCFILE
SUM COURSE_CODE COURSE_NAME
BY EMP_ID BY DATE_ATTEND
ON TABLE HOLD AS ORAEDUC FORMAT SQLORA
END

The following request joins the two Oracle tables and creates a left outer join (SET ALL = ON).

JOIN EMP_ID IN ORAEMP TO ALL EMP_ID IN ORAEDUC AS J1
SET ALL = ON
TABLE FILE ORAEMP
PRINT COURSE_CODE COURSE_NAME
BY EMP_ID
END

Since the join is an outer join, all ORAEMP rows display on the report output. ORAEMP rows with no corresponding ORAEDUC row display the missing data symbol for the fields from the ORAEDUC table.

EMP_ID     COURSE_CODE     COURSE_NAME  
------     -----------     -----------
071382660  101             FILE DESCRPT & MAINT          
112847612  101             FILE DESCRPT & MAINT          
           103             BASIC REPORT PREP FOR PROG    
117593129  101             FILE DESCRPT & MAINT          
           103             BASIC REPORT PREP FOR PROG    
           201             ADVANCED TECHNIQUES           
           203             FOCUS INTERNALS               
119265415  108             BASIC RPT NON-DP MGRS         
119329144  .               .                             
123764317  .               .                             
126724188  .               .                             
219984371  .               .                             
326179357  104             FILE DESC & MAINT NON-PROG    
           106             TIMESHARING WORKSHOP          
           102             BASIC REPORT PREP NON-PROG    
           301             DECISION SUPPORT WORKSHOP     
           202             WHAT'S NEW IN FOCUS           
451123478  101             FILE DESCRPT & MAINT          
543729165  .               .                             
818692173  107             BASIC REPORT PREP DP MGRS

The following request adds a screening condition on the ORAEDUC segment. To satisfy the screening condition, the course name must either contain the characters BASIC or be missing. 

JOIN CLEAR 
JOIN EMP_ID IN ORAEMP TO ALL EMP_ID IN ORAEDUC AS J1
SET ALL = ON
TABLE FILE ORAEMP
PRINT COURSE_CODE COURSE_NAME
BY EMP_ID
WHERE COURSE_NAME CONTAINS 'BASIC' OR COURSE_NAME IS MISSING
END

However, with SET ALL = ON, the rows with missing values are not retained on the report output.

EMP_ID     COURSE_CODE     COURSE_NAME      
------     -----------     -----------      
112847612  103             BASIC REPORT PREP FOR PROG    
117593129  103             BASIC REPORT PREP FOR PROG    
119265415  108             BASIC RPT NON-DP MGRS         
326179357  102             BASIC REPORT PREP NON-PROG    
818692173  107             BASIC REPORT PREP DP MGRS

The following request adds the SET SHORTPATH = SQL command.

JOIN CLEAR 
JOIN EMP_ID IN ORAEMP TO ALL EMP_ID IN ORAEDUC AS J1
SET ALL = ON
SET SHORTPATH=SQL
TABLE FILE ORAEMP
PRINT COURSE_CODE COURSE_NAME
BY EMP_ID
WHERE COURSE_NAME CONTAINS 'BASIC' OR COURSE_NAME IS MISSING
END

The report output now displays both the records containing the characters BASIC and those with missing values.

EMP_ID     COURSE_CODE     COURSE_NAME      
------     -----------     -----------      
112847612  103             BASIC REPORT PREP FOR PROG    
117593129  103             BASIC REPORT PREP FOR PROG    
119265415  108             BASIC RPT NON-DP MGRS         
119329144  .               .                             
123764317  .               .                             
126724188  .               .                             
219984371  .               .                             
326179357  102             BASIC REPORT PREP NON-PROG    
543729165  .               .                             
818692173  107             BASIC REPORT PREP DP MGRS
Example: Finding Host Records That Have No Matching Cross-Referenced Records

The following request counts and lists those employees who have taken no courses.

JOIN LEFT_OUTER EMP_ID IN ORAEMP TO ALL EMP_ID IN ORAEDUC AS J1
SET ALL = ON
SET SHORTPATH=SQL
TABLE FILE ORAEMP
COUNT EMP_ID
LIST EMP_ID LAST_NAME FIRST_NAME
WHERE COURSE_NAME IS MISSING
END

The output is:

EMP_ID                                           
COUNT    LIST  EMP_ID     LAST_NAME        FIRST_NAME
------   ----  ------     ---------        ---------- 
     5      1  119329144  BANNING          JOHN      
            2  123764317  IRVING           JOAN      
            3  126724188  ROMANS           ANTHONY   
            4  219984371  MCCOY            JOHN      
            5  543729165  GREENSPAN        MARY
Top of page
x
Testing for Missing Instances in FOCUS Data Sources

You can use the ALL PASS parameter to produce reports that include only parent instances with missing descendant values. To do so, write the request to screen out all existing instances in the segment with missing instances. After you set the ALL parameter to PASS, the report displays only the parent instances that are missing descendants.

Example: Testing for a MISSING Instance

The following request screens all salary instances, since no SALARY can both be equal to 0 and not equal to 0. 

SET ALL = PASS
TABLE FILE EMPLOYEE
PRINT LAST_NAME FIRST_NAME
WHERE SALARY EQ 0
WHERE SALARY NE 0
END

The output is:

LAST_NAME        FIRST_NAME
---------        ----------
DAVIS            ELIZABETH 
GARDNER          DAVID
Information Builders