|
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.
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:
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.
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.
Specifies a right outer join. The command SET SHORTPATH = SQL must be in effect.
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 simply used as segment references.
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.
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.
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: 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.
Is the cross-referenced Master File.
Is a field name in the cross-referenced Master File. It can be any field in the segment.
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.
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.
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.
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.
The following example joins the VIDEOTRK and MOVIES data sources on the conditions that:
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