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, 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|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:
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).
Note: Single line JOIN syntax is not supported. The END command is required.
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
WebFOCUS |