Conditional JOINs By Mohammed Zafrullah

We came across a situation where we had to join TABLEs that had more than an equality condition. One option was to use SQL passthru, where you could provide all the required filtered conditions and be sure that the query returned only the records that were needed. But we wanted to try to restrict the source code to TABLE FILE syntax. That's when we explored the option of using conditional JOINs.

Starting with WebFOCUS 5.2.x, you can establish JOINs based on conditions other than equality between fields. (This feature is supposed to be available as of WebFOCUS 4.3.6 CSP4 with iWay 5.1.4.) When using the conditional JOINs or the WHERE-based JOINs, the host and cross-referenced JOIN fields do not have to contain matching formats, and the cross-referenced field does not have to be indexed.

The syntax of the conditional (WHERE-based) JOIN command is

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

Note:

1. Single-line JOIN syntax is not supported.
2. You have to specify either ALL or ONE after TO.
3. The END command is required.

The conditional JOIN is supported for FOCUS and for VSAM, ADABAS, IMS, and all relational data sources. Because each data source differs in its ability to handle complex WHERE criteria, the optimization of the conditional JOIN syntax differs depending on the specific data sources involved in the JOIN and the complexity of the WHERE criteria.

Therefore, when using conditional JOINs, it is good to have a SQL trace-enabled to check if the query that is generated is optimized. If the JOINs are not translated in the query that is sent to the data source, WebFOCUS retrieves the data from all the TABLEs and then applies the filter conditions. This would be highly undesirable when we speak of TABLEs having millions of rows.

Here is an example of a conditional JOIN using the TABLEs in sample pubs database of MS SQL Server.

-SET &ECHO=ALL;
-* -- -------------------------
-* -- SQL_TRACE
-* -- -------------------------
SET TRACEOFF=ALL
SET TRACEON=SQLAGGR//CLIENT
SET TRACEON=STMTRACE//CLIENT
SET TRACEON=STMTRACE/2/CLIENT
SET TRACEUSER=ON
-* -- -------------------------
-* -- JOIN TABLES
-* -- -------------------------
JOIN FILE TITLES   AT TITLE_ID TAG TTL TO ALL
     FILE ROYSCHED AT TITLE_ID TAG ROY AS J0
     WHERE TTL.TITLE_ID  EQ ROY.TITLE_ID;
     WHERE TTL.YTD_SALES GE ROY.LORANGE;
     WHERE TTL.YTD_SALES LE ROY.HIRANGE;
END
-* -- -------------------------
-* -- EXTRACT DATA
-* -- -------------------------
TABLE FILE TITLES
PRINT
 TITLE_ID
 YTD_SALES
 LORANGE
 HIRANGE
 ROYALTY
ON TABLE HOLD
END
-RUN

The SQL that is generated from the above code is:

11.47.16 BR  (FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
11.47.16 BR  (FOC2594) AGGREGATION IS NOT APPLICABLE TO THE VERB USED
11.47.16 AE    SELECT T1."title_id",T1."royalty",T1."ytd_sales",T2."title_id",
11.47.16 AE   T2."lorange",T2."hirange" FROM pubs.dbo.titles T1,
11.47.16 AE   pubs.dbo.roysched T2 WHERE (T2."title_id" = T1."title_id") AND
11.47.16 AE   (T1."ytd_sales" >= T2."lorange") AND (T1."ytd_sales" <=
11.47.16 AE   T2."hirange");
0 NUMBER OF RECORDS IN TABLE=       16  LINES=     16
    

As you can see, the JOIN conditions are translated to T-SQL as we would expect it to do.

Conditional JOIN Processing

A WHERE in a JOIN is in effect only when a TABLE request references a cross-referenced segment or its children. If the request makes no such reference, the WHERE has no effect.

The AT attribute is used to link the correct parent segment or host to the correct child or cross-referenced segment. The field names used in the AT phrases are not used to cause the link. They are simply used as segment references.

Note: If there is no WHERE in effect, the JOIN will produce a Cartesian product.

You can also specify the conditional JOIN in the master file.

The above example can be altered as follows to use the joins in the master file as follows.

The master file would read:

FILE=TTLROY,    SUFFIX=SQLMSS
SEGNAME=TITLES, SEGTYPE=S0, $
  FIELD=TITLE_ID  ,ALIAS=title_id  ,USAGE=A6    ,ACTUAL=A6  ,$
  FIELD=TITLE     ,ALIAS=title     ,USAGE=A80   ,ACTUAL=A80 ,$
…
…
SEGNAME=ROYSCHED, SEGTYPE=KM, PARENT = TITLES,CRFILE = ROYSCHED,
     JOIN_WHERE=TITLES.TITLE_ID EQ ROYSCHED.TITLE_ID;
     JOIN_WHERE=YTD_SALES GE LORANGE;
     JOIN_WHERE=YTD_SALES LE HIRANGE;
  ,$

SEGNAME=TITLES, TABLENAME=pubs.dbo.titles, KEYS=1, CONNECTION=SQLDEV, $
SEGNAME=ROYSCHED, KEYFLD=TITLE_ID, TABLENAME=pubs.dbo.roysched, KEYS=0,
IXFLD=TITLE_ID, CONNECTION= SQLDEV,$

You can then use this master file as

TABLE FILE TTLROY
PRINT
 TITLE_ID
 YTD_SALES
 LORANGE
 HIRANGE
 ROYALTY
ON TABLE HOLD
END
-RUN

For further information on conditional JOINs, you can refer to the document "Creating Reports With WebFOCUS Language." The links are here (WebFOCUS 5.3) and here (WebFOCUS 7.1). More information regarding this article can be found at techsupport.informationbuilders.com/tech/ibm/ibm_nf_nf646.html.