In this section:
Reference: |
With the dynamic JOIN command, you can reference two or more related tables (or non-FOCUS data sources) in a single FOCUS report request. The data structures remain physically separate, but FOCUS treats them as a single logical structure.
Two types of join are available, the equijoin and the conditional (WHERE-based join).
The terms primary key and foreign key refer to the common columns that relate host and cross-referenced tables in an equijoin.
Although the run-time effect of the dynamic join is very similar to that of the embedded join discussed in Multi-Table Structures, the dynamic join is easier to construct since it does not require a separate Master File and Access File. You can create a dynamic join on an as-needed basis.
Note: A TABLE request that references a dynamically joined structure generates SQL join predicates for all segments in the subtree that starts from the root segment. Multi-table Master Files do not necessarily generate these predicates. In a multi-table structure, the subtree effectively begins with the highest referenced segment. This difference may cause identical TABLE requests to produce different reports when run against a dynamic join structure and a multi-table Master File that represent the same tree structure.
The dynamic join is limited to FOCUS read-only operations (for example, TABLE, GRAPH, and the MODIFY LOOKUP facility). Embedded joins in a Master File support both read-only and read-write operations.
FOCUS joins can be unique or non-unique. The difference between the two depends on the cross-referenced relation and its foreign key values:
The unique join is a FOCUS concept. The RDBMS makes no distinction between unique and non-unique situations. It always retrieves all matching rows from the cross-referenced file.
Note: If the RDBMS processes a join that the FOCUS request specifies as unique, and if there are, in fact, multiple corresponding rows in the cross-referenced file, the RDBMS inner join returns all matching rows. If, instead, optimization is disabled so that FOCUS processes the join, a different report results because FOCUS, respecting the unique join concept, returns only one cross-referenced row for each host row.
With either type of join, some rows in the host table may lack corresponding rows in the cross-referenced table. In a report request, such a retrieval path is called a short path.
When a report omits host rows that lack corresponding cross-referenced rows, the join is called an inner join. When a report displays all matching rows plus all rows from the host file that lack corresponding cross-referenced rows, the join is called a left outer join.
Sometimes FOCUS passes responsibility for a join to the RDBMS. The OPTIMIZATION setting is one factor in determining whether a join is optimized. Other factors depend on the specific elements in the report request (see The Adapter Optimizer). In order to understand join behavior, you must know whether optimization is enabled or disabled for a particular report request. The SQLAGGR trace gives you that information (see Tracing Adapter Processing).
For both embedded and dynamic joins, factors that determine whether a report includes short path rows are the type of join, the FOCUS SET ALL command, and whether FOCUS or the RDBMS is handling the join. Subsequent sections describe how these factors interact. SET ALL is described in The SET ALL Command.
By default, a JOIN command clears all DEFINE FILE commands for the host data source and the joined structure. Two methods are available for preserving virtual fields during join parsing, the KEEPDEFINES parameter and the DEFINE FILE SAVE and RETURN commands. For complete information, see your FOCUS documentation.
How to: |
A single-field dynamic equijoin is a join based on relating values in one column of the host and cross-referenced tables.
JOIN fielda1 [WITH rfield] IN hostfile [TAG tag1] TO [ALL] fieldb1 IN crfile [TAG tag2] AS joinname [END]
where:
Is a field in the host file or a DEFINE field that shares values and format with fieldb1 in the cross-referenced file.
Use only if fielda1 is a DEFINE field; associates the DEFINE field with the segment location of a real field (rfield) in the host file.
Is the name of the host file. Use this name in subsequent TABLE requests on the joined structure.
Is the tag name for the host file, a one- to eight-character table name qualifier for field and alias names in the host file. The tag name for the host must be the same in all the JOIN commands for a join structure. The tag name may not be the same as any table name in the structure.
Indicates that the host and cross-referenced files participate in a one-to-many or many-to-many relationship. That is, for any value of the join field in the host file (fielda1), there may be more than one corresponding instance of that value for the join field in the cross-referenced file (fieldb1). In FOCUS terminology, this is known as a non-unique or multiple join.
Note: The use of the ALL parameter does not disable optimization. Do not confuse this ALL parameter with the SET ALL command discussed in The SET ALL Command.
Omitting the ALL parameter indicates a unique join. Omit the ALL parameter only when each row in the host file has, at most, one corresponding row in the cross-referenced file. The unique join is a FOCUS concept. The RDBMS makes no distinction between unique and non-unique situations when it processes a join.
Is a field in the cross-referenced file whose values and format can match that of fielda1.
Is the name of the cross-referenced file.
Is the tag name for the cross-referenced file, a one- to eight-character table name qualifier for field and alias names in the cross-referenced file. The tag name may not be the same as any table name in the structure. In a recursive join structure, if no tag name is provided, FOCUS prefixes all field names and aliases with the first four characters of the join name.
Assigns an internal name to the join structure, up to eight characters in length. Joinname also provides a unique prefix for field names participating in a recursive join.
You can clear the join name when you no longer need this join (see JOIN CLEAR). Do not specify joinname as the file name in subsequent TABLE FILE requests; use hostfile.
Required when the JOIN command is longer than one line. Terminates the statement.
JOIN EID IN EMPINFO TAG FILE1 TO ALL PAYEID IN PAYINFO TAG FILE2 AS JOIN1
You can join up to 1024 structures to create a FOCUS view of the data. The joined structure remains in effect for the duration of the FOCUS session or until you clear the join name using the JOIN CLEAR command (see JOIN CLEAR). The adapter instructs the RDBMS to perform a join based on the smallest subtree of referenced tables (from the root) required to satisfy the request.
Note: For a join to be optimized, it cannot involve more than the RDBMS limit for an SQL statement. It also cannot involve more than the FOCUS limit for files in a join.
Each cross-referenced table must have at least one data field in common with its host file. Fixed sequential, ISAM, VSAM, IMS, CA-IDMS/DB, CA-Datacom/DB®, ADABAS®, Teradata, MODEL 204®, Oracle, and RDBMS tables and views can be both host files and cross-referenced files in any combination. You can also join these files to all segments of a FOCUS data source by using FOCUS database indexed fields.
If the DB2 Distributed Data Facility is installed:
The following chart lists some of the more common combinations available with the FOCUS dynamic JOIN command and restrictions on their use. Consult your FOCUS documentation on creating reports for additional data source combinations and examples of dynamic joins. In the chart, SQL refers to DB2, Teradata, IDMS SQL, or Oracle tables and views:
From | To | Special Rules That May Apply |
|---|---|---|
SQL | SQL | Joined fields must be of the same data type. For efficient retrieval, their lengths should also be equivalent. Use of indexed fields for the host and cross-referenced fields is recommended, but the RDBMS uses the indexes only if both the data types and lengths are equal. |
SQL | FOCUS | Must join to an indexed field (FIELDTYPE=I). Joined fields must have common data type and length. |
SQL | IMS | Adapter for IMS must be installed. The DL/I join field must be a key field in the root segment of the data source. It can be a primary or secondary index. Join fields must have a common data type and length. |
SQL | VSAM | For a unique join, the VSAM join field must be a full primary key. For a non-unique join, the join field can be an initial subset of the primary key. Joined fields must have a common data type and length. |
SQL | QSAM | QSAM file must be sorted by its join field. Joined fields must have a common data type and length. |
VSAM | SQL | Joined fields must have a common data type and length. |
QSAM | SQL | Joined fields must have a common data type and length. |
Note:
The following examples illustrate the use of the dynamic JOIN command. Each example specifies a non-unique join and presents an equivalent SQL request for comparison purposes. Both forms of the request, FOCUS and SQL, return the same result.
Note:
The first example prints the full name and courses taken for each employee. Since the employee may have taken more than one course, the example specifies the FOCUS non-unique join.
Employee information is stored in the DB2 table EMPINFO and is represented by the fields LAST_NAME and FIRST_NAME. Course information is stored in the DB2 table COURSE and is represented by the CNAME field.
SET TRACEUSER = ON
SET TRACEOFF = ALL
SET TRACEON = STMTRACE//CLIENT
JOIN EMP_ID IN EMPINFO TAG FILE1 TO ALL WHO IN COURSE TAG FILE2 AS J1
TABLE FILE EMPINFO
PRINT CNAME
BY LAST_NAME BY FIRST_NAME
END
SELECT T1.EID,T1.LN,T1.FN,T2.COURSE_NAME FROM
"USER1"."EMPINFO" T1,"USER1"."COURSE" T2 WHERE (T2.EMP_NO =
T1.EID) ORDER BY T1.LN,T1.FN FOR FETCH ONLY;The SQL request references both tables in the FROM clause and as a condition (or join predicate) of the WHERE clause. The BY phrases translate to the ORDER BY phrase. The PRINT command translates as SELECT.
The next example illustrates a screening test that lists the employees who have taken either the Advanced or Internals course.
SET TRACEUSER = ON
SET TRACEOFF = ALL
SET TRACEON = STMTRACE//CLIENT
JOIN WHO IN COURSE TAG FILE1 TO ALL EMP_ID IN EMPINFO TAG FILE2 AS J2
TABLE FILE COURSE
PRINT CNAME
BY LAST_NAME BY FIRST_NAME
WHERE CNAME IS 'ADVANCED' OR 'INTERNALS'
END
SELECT T1.COURSE_NAME,T1.EMP_NO,T2.LN,T2.FN FROM
"USER1"."COURSE" T1,"USER1"."EMPINFO" T2 WHERE (T2.EID =
T1.EMP_NO) AND (T1.COURSE_NAME IN('ADVANCED','INTERNALS'))
ORDER BY T2.LN,T2.FN FOR FETCH ONLY;The SQL request references both tables in the FROM clause and in a join predicate in the WHERE clause (along with additional screening conditions). The BY phrases translate to the ORDER BY phrase.
How to: |
You can construct a dynamic join based on multiple fields from the host file and cross-referenced files. Separate the participating field names with the keyword AND.
JOIN fielda1 AND fielda2 IN hostfile [TAG tag1] TO [ALL] fieldb1 AND fieldb2 IN crfile [TAG tag2] AS joinname [END]
where:
Is a field in the host file that shares values and format with fieldb1 in the cross-referenced file.
Is a field in the host file that shares values and format with fieldb2 in the cross-referenced file.
Is the name of the host file. Use this name in subsequent TABLE requests on the joined structure. The host file can be any type of database or table. See Constructing a Single-field Dynamic Equijoin for possible join combinations.
Is the tag name for the host file, a one- to eight-character table name qualifier for field and alias names in the host file. The tag name for the host must be the same in all the JOIN commands for a join structure. The tag name may not be the same as any table name in the structure
Indicates that the host and cross-referenced files participate in a one-to-many or many-to-many relationship. That is, for any instance of the join fields in the host file (fielda1, fielda2), there may be more than one corresponding instance of the join fields in the cross-referenced file (fieldb1, fieldb2). In FOCUS terminology, this is known as a non-unique or multiple join.
Note: The use of the ALL parameter does not disable optimization. Do not confuse this ALL parameter with the SET ALL command discussed in The SET ALL Command.
Omitting the ALL parameter indicates a unique join. Omit the ALL parameter only when each row in the host file has, at most, one corresponding row in the cross-referenced file. The unique join is a FOCUS concept. The RDBMS makes no distinction between unique and non-unique situations when it processes a join.
Is a field in the cross-referenced file whose values and format can match that of fielda1.
Is a field in the cross-referenced file whose values and format can match that of fielda2.
Is the name of the cross-referenced file.
Is the tag name for the cross-referenced file, a one- to eight-character table name qualifier for field and alias names in the cross-referenced file. The tag name may not be the same as any table name in the structure. In a recursive join structure, if no tag name is provided, FOCUS prefixes all field names and aliases with the first four characters of the joinname.
Assigns an internal name to the join structure, up to eight characters in length. It also provides a unique prefix for field names participating in a recursive join.
You can clear the join name when you no longer need this join (see JOIN CLEAR). Do not specify joinname as the file name in subsequent TABLE FILE requests. Use hostfile.
Required when the JOIN command is longer than one line. Terminates the command.
For a multi-field join, the joined fields from each table must be equal in number, data type, and field length. The full set of common fields must reside in both the host and cross-referenced tables.
You can join a maximum of 16 fields from a host file to 16 fields in a cross-referenced file. Each multi-field JOIN command counts as one join toward the FOCUS maximum of 1023 concurrent joins.
Note: For a join to be optimized, it cannot involve more than 15 tables or views, the RDBMS limit for an SQL statement.
For example, two RDBMS tables, EMPINFO and COURSE1, have first and last name fields. In the EMPINFO Master File, LAST_NAME and FIRST_NAME have field formats A15 and A10:
FILENAME=EMPINFO ,SUFFIX=SQLDS,$ SEGNAME=EMPINFO ,SEGTYPE=S0,$ FIELD=EMP_ID ,ALIAS=EID ,USAGE=A9 ,ACTUAL=A9,$ FIELD=LAST_NAME ,ALIAS=LN ,USAGE=A15 ,ACTUAL=A15,$ FIELD=FIRST_NAME ,ALIAS=FN ,USAGE=A10 ,ACTUAL=A10,$ FIELD=HIRE_DATE ,ALIAS=HDT ,USAGE=YMD ,ACTUAL=DATE,$ FIELD=DEPARTMENT ,ALIAS=DPT ,USAGE=A10 ,ACTUAL=A10, MISSING=ON,$ FIELD=CURRENT_SALARY ,ALIAS=CSAL ,USAGE=P9.2 ,ACTUAL=P4,$ FIELD=CURR_JOBCODE ,ALIAS=CJC ,USAGE=A3 ,ACTUAL=A3,$ FIELD=ED_HRS ,ALIAS=OJT ,USAGE=F6.2 ,ACTUAL=F4, MISSING=ON,$ FIELD=BONUS_PLAN ,ALIAS=BONUS_PLAN ,USAGE=I4 ,ACTUAL=I4,$ FIELD=HIRE_DATE_TIME ,ALIAS=HDTT ,USAGE=HYYMDm ,ACTUAL=HYYMDm,$ FIELD=HIRE_TIME ,ALIAS=HT ,USAGE=HHIS ,ACTUAL=HHIS,$
In the COURSE1 Master File, LAST_NAME and FIRST_NAME have the same field formats, A15 and A10:
FILE=COURSE1 ,SUFFIX=SQLDS, $
SEGNAME=SEG01 ,SEGTYPE=S0, $
FIELDNAME =FOCLIST ,FOCLIST ,I5 ,I4 ,$
FIELDNAME =CNAME ,COURSE_NAME ,A15 ,A15 ,$
FIELDNAME =LAST_NAME ,LN ,A15 ,A15 ,$
FIELDNAME =FIRST_NAME ,FN ,A10 ,A10 ,$
FIELDNAME =GRADE ,GRADE ,A1 ,A1 ,
MISSING=ON, $
FIELDNAME =YR_TAKEN ,YR_TAKEN ,A2 ,A2 ,$
FIELDNAME =QTR ,QUARTER ,A1 ,A1 ,$To create the multi-field join, list both fields for each table with the keyword AND:
JOIN LAST_NAME AND FIRST_NAME IN EMPINFO TAG FILE1 TO ALL LAST_NAME AND FIRST_NAME IN COURSE1 TAG FILE2 AS J1 END
When a report request references the multi-field dynamic equijoin, the adapter generates an SQL SELECT statement to satisfy the request:
SET TRACEUSER = ON
SET TRACEOFF = ALL
SET TRACEON = STMTRACE//CLIENT
TABLE FILE EMPINFO
PRINT LAST_NAME FIRST_NAME CNAME
END
SELECT T1.LN,T1.FN,T2.COURSE_NAME FROM "USER1"."EMPINFO" T1,
"USER1"."COURSE1" T2 WHERE (T2.LN = T1.LN) AND (T2.FN = T1.FN) FOR
FETCH ONLY;The SQL SELECT statement implements two joins as conditions (or predicates) of the WHERE clause.
In this section: How to: |
The conditional join lets you specify conditions other than equality between fields for relating two tables.
The conditional JOIN command supports FOCUS, relational, VSAM, fixed-format sequential, ADABAS, and IMS data sources. Because each data source differs in its ability to handle complex WHERE criteria, the optimization of the WHERE-based JOIN syntax differs depending on the specific data sources involved in the join and the complexity of the WHERE criteria.
JOIN FILE from_file AT from_field [TAG from_tag] [WITH fieldname] TO [ALL|ONE] FILE to_file AT to_field [TAG to_tag] [AS as_name] [WHERE expression1 ; WHERE expression2 ; ... ;] END
where:
Is the host Master File.
Is the field name in the host Master File whose segment will be joined to the cross-referenced data source. It can be any field in the segment. It must reside in the lowest level segment that will be referenced.
Is the optional tag name that is used as a unique qualifier for fields and aliases in the host data source.
Is a real field name used to assign a segment location for a virtual field. Required when issuing a DEFINE field-based WHERE-based JOIN.
Describes a one-to-many relationship between the from_file and to_file.
Describes a one-to-one relationship between the from_file and to_file.
Note: If you specify a unique join when the relationship between the host and cross-referenced files is one-to-many, the results will be unpredictable.
Is the cross-referenced Master File.
Is the join field name in the cross-referenced Master File. It can be any field in the segment.
Is the optional tag name that is used as a unique qualifier for fields and aliases in the cross-referenced data source.
Is the name associated with the JOIN.
Are any expressions valid in a DEFINE FILE command. All of the fields used in all of the expressions the expressions must lie on a single path.
Note: Single line JOIN syntax is not supported. The END command is required.
To issue a DEFINE-based conditional join, the KEEPDEFINES setting must be ON. You then must create all virtual fields before issuing the join. This differs from traditional DEFINE-based joins in which the virtual field is created after the JOIN command is issued. In addition, a virtual field can be part of the JOIN syntax or WHERE criteria. For a discussion of the KEEPDEFINES setting, see your FOCUS documentation.
SET TRACEUSER = ON SET TRACEOFF = ALL SET TRACEON = STMTRACE//CLIENT JOIN FILE EMPINFO AT EMP_ID TAG E1 TO ALL FILE PAYINFO AT PAYEID TAG P1 AS JW WHERE EMP_ID EQ PAYEID; WHERE (DAT_INC - HIRE_DATE) GT 0; WHERE (DAT_INC - HIRE_DATE) LT 365; END TABLE FILE EMPINFO PRINT CURRENT_SALARY COMPUTE DIFF/I5 = DAT_INC - HIRE_DATE; END
For DB2, the following SQL is generated:
SELECT T1."EID",T1."HDT",T1."CSAL",T2."EID",T2."DI" FROM "USER1"."EMPINFO" T1,"USER1"."PAYINFO" T2 WHERE (T2."EID" = T1."EID") AND ((DAYS(T2."DI") - DAYS(T1."HDT")) > 0) AND ((DAYS(T2."DI") - DAYS(T1."HDT")) < 365) FOR FETCH ONLY;
The SQL request references both tables in the FROM clause and incorporates the conditions specified in the JOIN command as predicates of the WHERE clause. The PRINT command translates as SELECT.
A left outer join selects all records from the host table and matches them with records from the cross-referenced table. When no matching records exist, the host record is still retained, and default values (blank or zero) are assigned to the cross-referenced fields. The relational adapters may optimize any WHERE-based left outer join command in which the conditional expressions are supported by the RDBMS.
JOIN LEFT_OUTER FILE hostfile AT hfld1 [TAG tag1] [WITH hfld2] TO {UNIQUE|MULTIPLE} FILE crfile AT crfld [TAG tag2] [AS joinname] [WHERE expression1; [WHERE expression2; ...] END
where:
Specifies a left outer join. If you do not specify the type of join in the JOIN command, the ALL parameter setting determines the type of join to perform.
Is the host Master File.
Links the correct parent segment or host to the correct child or cross-referenced segment. The field values used as the AT parameter are not used to cause the link. They are used as segment references.
Is the field name in the host Master File whose segment will be joined to the cross-referenced data source. The field name must be at the lowest level segment in the data source that is referenced.
Is the optional tag name that is used as a unique qualifier for fields and aliases in the host data source.
Is a data source field with which to associate a DEFINE-based conditional join. For a DEFINE-based conditional join, the KEEPDEFINES setting must be ON, and you must create the virtual fields before issuing the JOIN command.
Specifies a one-to-many relationship between hostfile and crfile. Note that ALL is a synonym for MULTIPLE.
Specifies a one-to-one relationship between hostfile and crfile. Note that ONE is a synonym for UNIQUE.
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).
The unique join is a FOCUS concept. The RDBMS makes no distinction between unique and non-unique situations; it always retrieves all matching rows from the cross-referenced file.
If the RDBMS processes a join that the request specifies as unique, and if there are, in fact, multiple corresponding rows in the cross-referenced file, the RDBMS returns all matching rows. If, instead, optimization is disabled so that FOCUS processes the join, a different report results because FOCUS, respecting the unique join concept, returns only one cross-referenced row for each host row.
Is the cross-referenced Master File.
Is the join field name in the cross-referenced Master File. It can be any field in the segment.
Is the optional tag name that is used as a unique qualifier for fields and aliases in the cross-referenced data source.
Is the name associated with the joined structure.
Are any expressions that are acceptable in a DEFINE FILE command. All fields used in the expressions must lie on a single path.
or
or
| Information Builders |