Using a Conditional Join

How to:

Using conditional JOIN syntax, you can establish joins based on conditions other than equality between fields. In addition, 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 conditional join is supported for FOCUS and for VSAM, ADABAS, IMS, IDMS, 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.

The standard ? JOIN command lists every join currently in effect, and indicates any that are based on WHERE criteria.

Syntax: How to Create a Conditional JOIN

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

JOIN [LEFT_OUTER|RIGHT_OUTER|INNER] FILE hostfile AT hfld1     [WITH hfld2] [TAG tag1]
     TO {UNIQUE|MULTIPLE} 
     FILE crfile AT crfld [TAG tag2] [AS joinname]
     [WHERE expression1;
     [WHERE expression2;
     ...]
END

where:

INNER

Specifies an inner 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.

LEFT_OUTER

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.

RIGHT_OUTER

Specifies a right outer join. The command SET SHORTPATH = SQL must be in effect.

hostfile

Is the host Master File.

AT

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 simply used as segment references.

hfld1

Is a 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 its data source that is referenced.

tag1

Is the optional tag name of up to 66 characters that is used as a unique qualifier for fields and aliases in the host data source.

hfld2

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.

MULTIPLE

Specifies a one-to-many relationship between hostfile and crfile. Note that ALL is a synonym for MULTIPLE.

UNIQUE

Specifies a one-to-one relationship between hostfile and crfile. Note that ONE is a synonym for UNIQUE.

Note: Regardless of the character of the JOIN—INNER or LEFT_OUTER—the join to UNIQUE will return only one instance of the cross-referenced file, and if this instance does not match based on the evaluation of the WHERE expression, default values (spaces for alphanumeric fields and 0 for numerical fields) are returned. There are never short paths or missing values in the cross-referenced file.

crfile

Is the cross-referenced Master File.

crfld

Is a field name in the cross-referenced Master File. It can be any field in the segment.

tag2

Is the optional tag name of up to 66 characters that is used as a unique qualifier for fields and aliases in the cross-referenced data source.

joinname

Is the name associated with the joined structure.

expression1, expression2

Are any expressions that are acceptable in a DEFINE FILE command. All fields used in the expressions must lie on a single path.

You must include the connection between the tables in the WHERE conditions. The AT references do not actually perform a JOIN between the fields as with a standard JOIN.

If you do not include any WHERE conditions in the join, a cartesian product is generated.

END

The END command is required to terminate the command and must be on a line by itself.

Note: Single line JOIN syntax is not supported.

Example: Using a Conditional Join

The following example joins the VIDEOTRK and MOVIES data sources on the conditions that:

  • The transaction date (in VIDEOTRK) is more than ten years after the release date (in MOVIES).
  • The movie codes match in both data sources.

The join is performed at the segment that contains MOVIECODE in the VIDEOTRK data source, because the join must occur at the lowest segment referenced.

The following request displays the title, most recent transaction date, and release date for each movie in the join, and computes the number of years between this transaction date and the release date:

JOIN FILE VIDEOTRK AT MOVIECODE TAG V1 TO ALL 
     FILE MOVIES   AT RELDATE   TAG M1 AS JW1
  WHERE DATEDIF(RELDATE, TRANSDATE,'Y') GT 10;
  WHERE V1.MOVIECODE EQ M1.MOVIECODE;
END
TABLE FILE VIDEOTRK
 SUM TITLE/A25 AS 'Title'
     TRANSDATE AS 'Last,Transaction'
     RELDATE AS 'Release,Date'
 COMPUTE YEARS/I5 = (TRANSDATE - RELDATE)/365;  AS 'Years,Difference'
 BY TITLE NOPRINT
 BY HIGHEST 1 TRANSDATE NOPRINT
END

The output is:

                           Last         Release   Years
Title                      Transaction  Date      Difference
-----                      -----------  -------   ----------
ALICE IN WONDERLAND        91/06/22     51/07/21          39
ALIEN                      91/06/18     80/04/04          11
ALL THAT JAZZ              91/06/25     80/05/11          11
ANNIE HALL                 91/06/24     78/04/16          13
BAMBI                      91/06/22     42/07/03          49
BIRDS, THE                 91/06/23     63/09/27          27
CABARET                    91/06/25     73/07/14          17
CASABLANCA                 91/06/27     42/03/28          49
CITIZEN KANE               91/06/22     41/08/11          49
CYRANO DE BERGERAC         91/06/20     50/11/09          40
DEATH IN VENICE            91/06/26     73/07/27          17
DOG DAY AFTERNOON          91/06/23     76/04/04          15
EAST OF EDEN               91/06/20     55/01/12          36
GONE WITH THE WIND         91/06/24     39/06/04          52
JAWS                       91/06/27     78/05/13          13
MALTESE FALCON, THE        91/06/19     41/11/14          49
MARTY                      91/06/19     55/10/26          35
NORTH BY NORTHWEST         91/06/21     59/02/09          32
ON THE WATERFRONT          91/06/24     54/07/06          36
PHILADELPHIA STORY, THE    91/06/21     40/05/06          51
PSYCHO                     91/06/17     60/05/16          31
REAR WINDOW                91/06/17     54/12/15          36
SHAGGY DOG, THE            91/06/25     59/01/09          32
SLEEPING BEAUTY            91/06/24     75/08/30          15
TIN DRUM, THE              91/06/17     80/03/01          11
VERTIGO                    91/06/27     58/11/25          32