How to: Reference: |
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 (called a short path), the report output displays missing values for the fields from the cross-referenced segment. However, the fields are not assigned missing values for testing purposes.
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.
SET SHORTPATH = {FOCUS|SQL}where:
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.
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).
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.
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 MGRSThe 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
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
| Information Builders |