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.
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
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
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.
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
How to: |
You can include parent instances with missing descendants by issuing the SET ALL parameter before executing the request.
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 the report, use the SET ALL=PASS command.
SET ALL= {OFF|ON|PASS}where:
Omits parent instances that are missing descendants from the report. OFF is the default value.
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.
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).
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
ENDThe 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
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.
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 |