In this section: |
How to: |
Reference: |
The most common joined structures are based on real fields that have been declared in the Master Files of the host and cross-referenced data sources, respectively.
The following JOIN syntax requires that the fields you are using to join the files are real fields declared in the Master File. This join may be a simple one based on one field in each file to be joined, or a multi-field join for data sources that support this type of behavior. The following syntax describes the simple and multi-field variations:
JOIN [LEFT_OUTER|RIGHT_OUTER|INNER] hfld1 [AND hfld2 ...] IN hostfile [TAG tag1] TO [UNIQUE|MULTIPLE] crfield [AND crfld2 ...] IN crfile [TAG tag2] [AS joinname] END
where:
Note that in a left outer join, host records with a missing cross-referenced instance are included in the report output. To control how tests against missing cross-referenced segment instances are processed, use the SET SHORTPATH command described in Handling a Missing Segment Instance.
Note that in a right outer join, cross-referenced records with a missing host instance are included in the report output.
The tag name for the host file must be the same in all the JOIN commands of a joined structure.
Note: Unique returns only one instance and, if there is no matching instance in the cross-referenced file, it supplies default values (blank for alphanumeric fields and zero for numeric fields).
Use the MULTIPLE parameter when crfld1 may have multiple instances in common with one value in hfld1. Note that ALL is a synonym for MULTIPLE, and omitting this parameter entirely is a synonym for UNIQUE. See Unique and Non-Unique Joined Structures for more information.
Note: crfld2 may be qualified. This field is only available for data adapters that support multi-field joins.
The tag name for the host file must be the same in all the JOIN commands of a joined structure.
Note: If you do not assign a name to the join structure with the AS phrase, the name is assumed to be blank. A join without a name overwrites an existing join without a name.
An example of a simple unique join is shown below:
JOIN JOBCODE IN EMPLOYEE TO JOBCODE IN JOBFILE AS JJOIN
The following procedure creates three FOCUS data sources:
The procedure then adds an employee to EMPINFO named Fred Newman who has no matching record in the JOBINFO or EDINFO data sources.
TABLE FILE EMPLOYEE SUM LAST_NAME FIRST_NAME CURR_JOBCODE BY EMP_ID ON TABLE HOLD AS EMPINFO FORMAT FOCUS INDEX EMP_ID CURR_JOBCODE END -RUN TABLE FILE JOBFILE SUM JOB_DESC BY JOBCODE ON TABLE HOLD AS JOBINFO FORMAT FOCUS INDEX JOBCODE END -RUN TABLE FILE EDUCFILE SUM COURSE_CODE COURSE_NAME BY EMP_ID ON TABLE HOLD AS EDINFO FORMAT FOCUS INDEX EMP_ID END -RUN MODIFY FILE EMPINFO FREEFORM EMP_ID LAST_NAME FIRST_NAME CURR_JOBCODE MATCH EMP_ID ON NOMATCH INCLUDE ON MATCH REJECT DATA 111111111, NEWMAN, FRED, C07,$ END
The following request prints the contents of EMPINFO. Note that Fred Newman has been added to the data source:
TABLE FILE EMPINFO PRINT * END
The output is:
EMP_ID LAST_NAME FIRST_NAME CURR_JOBCODE ------ --------- ---------- ------------ 071382660 STEVENS ALFRED A07 112847612 SMITH MARY B14 117593129 JONES DIANE B03 119265415 SMITH RICHARD A01 119329144 BANNING JOHN A17 123764317 IRVING JOAN A15 126724188 ROMANS ANTHONY B04 219984371 MCCOY JOHN B02 326179357 BLACKWOOD ROSEMARIE B04 451123478 MCKNIGHT ROGER B02 543729165 GREENSPAN MARY A07 818692173 CROSS BARBARA A17 111111111 NEWMAN FRED C07
The following JOIN command creates an inner join between the EMPINFO data source and the JOBINFO data source.
JOIN CLEAR * JOIN INNER CURR_JOBCODE IN EMPINFO TO MULTIPLE JOBCODE IN JOBINFO AS J0
Note that the JOIN command specifies a multiple join. In a unique join, the cross-referenced segment is never considered missing, and all records from the host file display on the report output. Default values (blank for alphanumeric fields and zero for numeric fields) display if no actual data exists.
The following request displays fields from the joined structure:
TABLE FILE EMPINFO PRINT LAST_NAME FIRST_NAME JOB_DESC END
Fred Newman is omitted from the report output because his job code does not have a match in the JOBINFO data source:
LAST_NAME FIRST_NAME JOB_DESC --------- ---------- -------- STEVENS ALFRED SECRETARY SMITH MARY FILE QUALITY JONES DIANE PROGRAMMER ANALYST SMITH RICHARD PRODUCTION CLERK BANNING JOHN DEPARTMENT MANAGER IRVING JOAN ASSIST.MANAGER ROMANS ANTHONY SYSTEMS ANALYST MCCOY JOHN PROGRAMMER BLACKWOOD ROSEMARIE SYSTEMS ANALYST MCKNIGHT ROGER PROGRAMMER GREENSPAN MARY SECRETARY CROSS BARBARA DEPARTMENT MANAGER
The following JOIN command creates a left outer join between the EMPINFO data source and the EDINFO data source:
JOIN CLEAR * JOIN LEFT_OUTER EMP_ID IN EMPINFO TO MULTIPLE EMP_ID IN EDINFO AS J1
The following request displays fields from the joined structure:
TABLE FILE EMPINFO PRINT LAST_NAME FIRST_NAME COURSE_NAME END
All employee records display on the report output. The records for those employees with no matching records in the EDINFO data source display the missing data character (.) in the COURSE_NAME column. If the join were unique, blanks would display instead of the missing data character.
LAST_NAME FIRST_NAME COURSE_NAME --------- ---------- ----------- STEVENS ALFRED FILE DESCRPT & MAINT SMITH MARY BASIC REPORT PREP FOR PROG JONES DIANE FOCUS INTERNALS SMITH RICHARD BASIC RPT NON-DP MGRS BANNING JOHN . IRVING JOAN . ROMANS ANTHONY . MCCOY JOHN . BLACKWOOD ROSEMARIE DECISION SUPPORT WORKSHOP MCKNIGHT ROGER FILE DESCRPT & MAINT GREENSPAN MARY . CROSS BARBARA HOST LANGUAGE INTERFACE NEWMAN FRED .
The following requests generate two Db2 tables to join, and then issues a request against the join.
The following request generates the WF_SALES table. The field ID_PRODUCT will be used in the right outer join command.
TABLE FILE WFLITE SUM GROSS_PROFIT_US PRODUCT_CATEGORY PRODUCT_SUBCATEG BY ID_PRODUCT WHERE ID_PRODUCT FROM 2150 TO 4000 ON TABLE HOLD AS WF_SALES FORMAT DB2 END
The following request generates the WF_PROD table. The field ID_PRODUCT will be used in the right outer join command.
TABLE FILE WFLITE SUM PRICE_DOLLARS PRODUCT_CATEGORY PRODUCT_SUBCATEG PRODUCT_NAME BY ID_PRODUCT WHERE ID_PRODUCT FROM 3000 TO 5000 ON TABLE HOLD AS WF_PROD FORMAT DB2 END
The following request issues the SET SHORTPATH=SQL and JOIN commands and displays values from the joined tables:
SET SHORTPATH = SQL JOIN RIGHT_OUTER ID_PRODUCT IN WF_PROD TAG T1 TO ALL ID_PRODUCT IN WF_SALES TAG T2 END TABLE FILE WF_PROD PRINT T1.ID_PRODUCT AS 'Product ID' PRICE_DOLLARS AS Price T2.ID_PRODUCT AS 'Sales ID' GROSS_PROFIT_US BY T1.ID_PRODUCT NOPRINT ON TABLE SET PAGE NOPAGE ON TABLE SET STYLE * GRID=OFF,$ ENDSTYLE END
You can generate a trace that shows the resulting SQL by adding the following commands.
SET TRACEUSER=ON SET TRACESTAMP=OFF SET TRACEOFF=ALL SET TRACEON = STMTRACE//CLIENT
The trace shows that the request was optimized as a right outer join to the RDBMS.
SELECT T1."ID_PRODUCT", T1."PRICE_DOLLARS", T2."ID_PRODUCT", T2."GROSS_PROFIT_US" FROM ( WF_PROD T1 RIGHT OUTER JOIN WF_SALES T2 ON T2."ID_PRODUCT" = T1."ID_PRODUCT" ) ORDER BY T1."ID_PRODUCT";
The output, shown in the following image, has a row for each ID_PRODUCT value that is in the WF_PRODUCT table. The columns from WF_SALES rows that do not have a matching ID_PRODUCT value display the NODATA symbol.
The following JOIN commands create an inner join between the EMPINFO and JOBINFO data sources and an inner join between the EMPINFO and EDINFO data sources:
JOIN CLEAR * JOIN INNER CURR_JOBCODE IN EMPINFO TO MULTIPLE JOBCODE IN JOBINFO AS J0 JOIN INNER EMP_ID IN EMPINFO TO MULTIPLE EMP_ID IN EDINFO AS J1
The structure created by the two joins has two independent paths:
SEG01 01 S1 ************** *EMP_ID **I *CURR_JOBCODE**I *LAST_NAME ** *FIRST_NAME ** * ** *************** ************** I +-----------------+ I I I SEG01 I SEG01 02 I KM 03 I KM .............. .............. :EMP_ID ::K :JOBCODE ::K :COURSE_CODE :: :JOB_DESC :: :COURSE_NAME :: : :: : :: : :: : :: : :: :............:: :............:: .............: .............: JOINED EDINFO JOINED JOBINFO
The following request displays fields from the joined structure:
SET MULTIPATH=SIMPLE TABLE FILE EMPINFO PRINT LAST_NAME FIRST_NAME IN 12 COURSE_NAME JOB_DESC END
With MULTIPATH=SIMPLE, the independent paths create independent joins. All employee records accepted by either join display on the report output. Only Fred Newman (who has no matching record in either of the cross-referenced files) is omitted:
LAST_NAME FIRST_NAME COURSE_NAME JOB_DESC --------- ---------- ----------- -------- STEVENS ALFRED FILE DESCRPT & MAINT SECRETARY SMITH MARY BASIC REPORT PREP FOR PROG FILE QUALITY JONES DIANE FOCUS INTERNALS PROGRAMMER ANALYST SMITH RICHARD BASIC RPT NON-DP MGRS PRODUCTION CLERK BANNING JOHN . DEPARTMENT MANAGER IRVING JOAN . ASSIST.MANAGER ROMANS ANTHONY . SYSTEMS ANALYST MCCOY JOHN . PROGRAMMER BLACKWOOD ROSEMARIE DECISION SUPPORT WORKSHOP SYSTEMS ANALYST MCKNIGHT ROGER FILE DESCRPT & MAINT PROGRAMMER GREENSPAN MARY . SECRETARY CROSS BARBARA HOST LANGUAGE INTERFACE DEPARTMENT MANAGER
With MULTIPATH=COMPOUND, only employees with matching records in both of the cross-referenced files display on the report output:
LAST_NAME FIRST_NAME COURSE_NAME JOB_DESC --------- ---------- ----------- -------- STEVENS ALFRED FILE DESCRPT & MAINT SECRETARY SMITH MARY BASIC REPORT PREP FOR PROG FILE QUALITY JONES DIANE FOCUS INTERNALS PROGRAMMER ANALYST SMITH RICHARD BASIC RPT NON-DP MGRS PRODUCTION CLERK BLACKWOOD ROSEMARIE DECISION SUPPORT WORKSHOP SYSTEMS ANALYST MCKNIGHT ROGER FILE DESCRPT & MAINT PROGRAMMER CROSS BARBARA HOST LANGUAGE INTERFACE DEPARTMENT MANAGER
The cross-referenced fields used in a JOIN must have the following characteristics in specific data sources:
Note: The indexed fields can be external. See the Describing Data manual for more information about indexed fields and the Rebuild tool.
When group fields are used in a joined structure, the group in the host file and the group in the cross-referenced file must have the same number of elements:
In VSAM ESDS data sources, the field can be any field, as long as the file is already sorted on that field.
In the Access File, the cross-referenced segment must specify ACCESS=ADBS and either CALLTYPE=FIND or CALLTYPE=RL. If CALLTYPE=RL, the host field can be joined to the high-order portion of a descriptor, superdescriptor, or subdescriptor, if the high-order portion is longer than the host field.
(FOC32452) Use of ALL. with LEFT_OUTER/INNER not allowed
How to: |
Reference: |
You can use DEFINE-based JOIN syntax to create a virtual host field that you can join to a real cross-referenced field. The DEFINE expression that creates the virtual host field may contain only fields in the host file and constants. (It may not contain fields in the cross-referenced file.) You can do more than one join from a virtual field.
You can create the virtual host field in a separate DEFINE command or in a Master File. For information on Master Files, see the Describing Data manual.
The same report request can use JOIN-based virtual fields, and virtual fields unrelated to the join.
Note that if you are creating a virtual field in a DEFINE command, you must issue the DEFINE after the JOIN command, but before the TABLE request since a JOIN command clears all fields created by DEFINE commands for the host file and the joined structure. Virtual fields defined in Master Files are not cleared.
Tip: If a DEFINE command precedes the JOIN command, you can set KEEPDEFINES ON to reinstate virtual fields during the parsing of a subsequent JOIN command. For more information, see Preserving Virtual Fields Using KEEPDEFINES.
The DEFINE-based JOIN command enables you to join a virtual field in the host file to a real field in the cross-referenced file. The syntax is:
JOIN [LEFT_OUTER|RIGHT_OUTER|INNER] deffld WITH host_field ... IN hostfile [TAG tag1] TO [UNIQUE|MULTIPLE] cr_field IN crfile [TAG tag2] [AS joinname] END
where:
Is the name of any real field in the host segment with which you want to associate the virtual field. This association is required to locate the virtual field.
Note: The WITH field referenced in the JOIN command must be in the same segment as the WITH field referenced in the DEFINE that creates the virtual field or no output will be produced.
The WITH phrase is required unless the KEEPDEFINES parameter is set to ON and deffld was defined prior to issuing the JOIN command.
To determine which segment contains the virtual field, use the ? DEFINE query after issuing the DEFINE command. See the Developing Applications manual for details about Query commands.
The tag name for the host file must be the same in all JOIN commands of a joined structure.
Note: UNIQUE returns only one instance and, if there is no matching instance in the cross-referenced file, it supplies default values (blank for alphanumeric fields and zero for numeric fields).
Use the MULTIPLE parameter when crfld1 may have multiple instances in common with one value in hfld1. Note that ALL is a synonym for MULTIPLE, and omitting this parameter entirely is a synonym for UNIQUE. See Unique and Non-Unique Joined Structures for more information.
The tag name for the host file must be the same in all JOIN commands of a joined structure.
If you do not assign a name to the joined structure with the AS phrase, the name is assumed to be blank. A join without a name overwrites an existing join without a name.
Requests reading joined data sources can contain virtual fields that are defined either:
DEFINE FILE hostfile
identifies the host data source in the joined structure.
Note: The expression defining the host field for the join can use only host fields and constants.
All other virtual fields can contain real fields from the host file and the cross-referenced file.
Tip: Since issuing the JOIN command clears all DEFINE commands for the host file and the joined structure, you must issue the DEFINE command after the JOIN or turn KEEPDEFINES ON to preserve the virtual fields. For more information, see Preserving Virtual Fields During Join Parsing.
Suppose that a retail chain sends four store managers to attend classes. Each person, identified by an ID number, manages a store in a different city. The stores and the cities in which they are located are contained in the SALES data source. The manager IDs, the classes, and dates the managers attended are contained in the EDUCFILE data source.
The following procedure lists the courses that the managers attended, identifying the managers by the cities in which they work. Note the three elements in the procedure:
The procedure is:
JOIN ID_NUM WITH CITY IN SALES TO ALL EMP_ID IN EDUCFILE AS SALEDUC
DEFINE FILE SALES ID_NUM/A9 = DECODE CITY ('NEW YORK' 451123478 'NEWARK' 119265415 'STAMFORD' 818692173 'UNIONDALE' 112847612); END
TABLE FILE SALES PRINT DATE_ATTEND BY CITY BY COURSE_NAME END
The output is:
CITY COURSE_NAME DATE_ATTEND ---- ----------- ----------- NEW YORK FILE DESCRPT & MAINT 81/11/15 NEWARK BASIC RPT NON-DP MGRS 82/08/24 STAMFORD BASIC REPORT PREP DP MGRS 82/08/02 HOST LANGUAGE INTERFACE 82/10/21 UNIONDALE BASIC REPORT PREP FOR PROG 81/11/16 FILE DESCRPT & MAINT 81/11/15
How to: |
The JOIN_LENGTH_MODE (JOINLM) parameter controls processing of equality joined field pairs for record-based non-SQL Adapters, such as DFIX, VSAM, and FIX. This setting controls processing when two alphanumeric fields of different lengths or two numeric fields of different data types and precisions are joined.
For SQL data sources, joins are normally either optimized (sent to the SQL engine for processing) or managed to comply with SQL processing rules.
There are two supported modes of handling compatible, but not identical, joined fields:
SET JOIN_LENGTH_MODE|JOINLM} = {SQL|RANGE}
where:
Sets SQL compliant mode. which assures strict equality between host and cross-referenced field values. This is the default value.
Sets FOCUS reporting mode, which supports partial key joins.
Generally, the fields containing the shared values in the host and cross-referenced files must have the same data formats.
If you specify multiple host file fields, the JOIN command treats the fields as one concatenated field. Add the field format lengths to obtain the length of the concatenated field. You must observe the following rules:
The formats may have different edit options.
Note that a text field cannot be used to join data sources.
The edit options may differ. The length may also differ, but with the following effect:
If the host field is not a group field, the cross-referenced field can still be a group. If the host field is a group, the cross-referenced field must also be a group.
How to: |
You can join two or more data sources containing different numeric data types. For example, you can join a field with a short packed decimal format to a field with a long packed decimal format, or a field with an integer format to a field with a packed decimal format. This provides enormous flexibility for creating reports from joined data sources.
Note:
To enable joins with data type conversion, issue the command
SET JOINOPT = [GNTINT|OLD]
where:
Since you can join a field with a short packed decimal format to a field with a long packed decimal format, a join can be defined in the following Master Files:
FILE=PACKED,SUFFIX=FIX,$ SEGNAME=ONE,SEGTYPE=S0 FIELD=FIRST,,P8,P4,INDEX=I,$
FILE=PACKED2,SUFFIX=FIX,$ SEGNAME=ONE,SEGTYPE=S0 FIELD=PFIRST,,P31,P16,INDEX=I,$
The JOIN command might look like this:
JOIN FIRST IN PACKED TO ALL PFIRST IN PACKED2 AS J1
When joining packed fields, the preferred sign format of X'C' for positive values and X'D' for negative values is still required. All other non-preferred signs are converted to either X'C' or X'D'.
|
Information Builders |