Handling a Missing Segment Instance

In this section:

Including Missing Instances in Reports With the ALL. Prefix
Including Missing Instances in Reports With the SET ALL Parameter
Testing for Missing Instances in TIBCO FOCUS Data Sources

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.

The top segment contains employee names.
The left segment contains addresses.
The right segment contains the salary history of each employee: the date the employee was granted a new salary, and the amount of the salary.

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.

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

Example: Reporting Against Segments With Descendant Instances

The following request displays the course codes and expenses for employees in the EMPDATA and TRAIN2 data sources. The report output displays all employees that have instances in the COURSECODE or EXPENSES fields. The employees that are missing instances for either of those fields are omitted from the report. For those employees that have instances for only one of the fields, the designator for missing data displays in the respective column. In this example, Henry Chisolm has taken two courses but only has expenses for one. Therefore, the designator for missing instances displays in the EXPENSES column.

JOIN EMPDATA.PIN IN EMPDATA TO ALL TRAINING.PIN IN TRAIN2 AS JOIN1
TABLE FILE EMPDATA
PRINT LASTNAME AND FIRSTNAME AND COURSECODE AND EXPENSES 
BY PIN
END

The output is:

PIN	LASTNAME	FIRSTNAME	COURSECODE	EXPENSES
000000010	VALINO	DANIEL	PDR740	2,300.00
000000030	CASSANOVA	LOIS	NAMA730	2,600.00
	CASSANOVA	LOIS	EDP090	2,300.00
. . .
000000350	FERNSTEIN	ERWIN	SSI220	1,850.00
	FERNSTEIN	ERWIN	MC90	1,730.00
	FERNSTEIN	ERWIN	UMI720	3,350.00
000000360	CHISOLM	HENRY	EDP090	.00
	CHISOLM	HENRY	EDP690	3,000.00
000000370	WANG	JOHN	UMI710	2,050.00
000000380	ELLNER	DAVID	EDP090	.
	ELLNER	DAVID	UNI780	3,350.00
000000410	CONTI	MARSHALL	EDP690	3,100.00

Note: The report output has been truncated for demonstration purposes.

Including Missing Instances in Reports With the SET ALL Parameter

How to:

Include a Parent Instance With Missing Descendants
Control Short Path Processing In a Left Outer Join

Reference:

Usage Notes for SET SHORTPATH = SQL

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, use the SET ALL=PASS command.

In WebFOCUS, 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 WebFOCUS processes those records when both of the following conditions apply:

There is a screening condition on the cross-referenced segment.
A host segment instance does not have a corresponding cross-referenced segment instance. This is called a short path.

When these two conditions are true, WebFOCUS 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 WebFOCUS 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.

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:

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

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

Testing for Missing Instances in TIBCO 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 in a TIBCO FOCUS Data Source

The following request tests for missing instances in the COURSECODE field. Since no COURSECODE can equal 'XXXX', only employees with missing instances in COURSECODE display in the report output.

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 COURSECODE EQ 'XXXX'
END

The output is:

PIN	LASTNAME	FIRSTNAME	COURSECODE	EXPENSES
000000020	BELLA	MICHAEL	.	.
000000060	PATEL	DORINA	.	.
000000070	SANCHEZ	EVELYN	.	.
000000090	PULASKI	MARIANNE	.	.
000000130	CVEK	MARCUS	.	.
000000170	MORAN	WILLIAM	.	.
000000220	LEWIS	CASSANDRA	.	.
000000230	NOZAWA	JIM	.	.
000000300	SOPENA	BEN	.	.
000000390	GRAFF	ELAINE	.	.
000000400	LOPEZ	ANNE	.	.

Handling a Missing Segment Instance

Example: Reporting Against Segments Without Descendant Instances

Example: Reporting Against Segments With Descendant Instances

Including Missing Instances in Reports With the ALL. Prefix

Example: Including Missing Segment Instances With the ALL. Prefix

Including Missing Instances in Reports With the SET ALL Parameter

Syntax: How to Include a Parent Instance With Missing Descendants

Example: Including Missing Segment Instances With SET ALL

Syntax: How to Control Short Path Processing In a Left Outer Join

Reference: Usage Notes for SET SHORTPATH = SQL

Example: Controlling Outer Join Processing

Example: Finding Host Records That Have No Matching Cross-Referenced Records

Testing for Missing Instances in TIBCO FOCUS Data Sources

Example: Testing for a MISSING Instance in a TIBCO FOCUS Data Source