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. |
In this section: Constructing a Single-field Dynamic Equijoin Constructing a Multi-field Dynamic Equijoin Constructing a Conditional Join Reference: |
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 Data 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 Data 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.
A single-field dynamic equijoin is a join based on relating values in one column of the host and cross-referenced tables. |
How to: Construct a Single- Field Dynamic Equijoin Example: |
JOIN fielda1 [WITH rfield] IN hostfile [TAG tag1] TO [ALL]
fieldb1 IN crfile [TAG tag2] AS joinname
[END]
where:
Note:
JOIN EID IN EMPINFO TAG FILE1 TO ALL PAYEID IN PAYINFO TAG FILE2 AS JOIN1
You can join up to 16 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 data 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 15 tables or views, the RDBMS limit for an SQL statement.
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, DB2 for VM, 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 |
XMI/DLI, XMI/MVS/IMS, or IMS Data Adapter 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 each employee's full name and courses taken. 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.
You can construct a dynamic join based on multiple fields from the host file and cross-referenced files. Separate the participating fieldnames with the keyword AND. |
How to: Construct a Multi- Field Dynamic Equijoin Example: |
JOIN fielda1 AND fielda2 IN hostfile [TAG tag1] TO [ALL]
fieldb1 AND fieldb2 IN crfile [TAG tag2] AS joinname
[END]
where:
Note:
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 16 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 ,$
Note: File Descriptions and Tables, does not include the COURSE1 Master File. To reproduce the COURSE1 Master File, extract the fields from the ECOURSE Master File and use the HOLD FORMAT SQL option explained in Creating Tables Using the HOLD Command.
To create the multi-field join, list both fields for each table with the keyword AND:
JOIN LAST_NAME AND FISRT_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 data 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.
The conditional join lets you specify conditions other than equality between fields for relating two tables. |
How to: Example: |
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:
Note:
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.
| Information Builders |