quote:

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.


--------------------------------------------------------------------------------
Top of page
--------------------------------------------------------------------------------


Syntax: How to Create a Conditional JOIN
The syntax of the conditional (WHERE-based) JOIN command is

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

APP FI MASTER DISK LOOK.MAS
-RUN
-WRITE MASTER FILENAME=LOOK, SUFFIX=FOC
-WRITE MASTER SEGNAME=ONE, SEGTYPE=S1
-WRITE MASTER FIELDNAME=BLANK       ,ALIAS=    ,USAGE=A1   ,INDEX=I ,$
-WRITE MASTER SEGNAME=TWO, SEGTYPE=S1
-WRITE MASTER FIELDNAME=LU_FIELD    ,ALIAS=    ,USAGE=P6.4 ,$
-WRITE MASTER FIELDNAME=RT_FIELD    ,ALIAS=    ,USAGE=P4.2 ,$
CREATE FILE LOOK
MODIFY FILE LOOK
FREEFORM BLANK LU_FIELD RT_FIELD
DATA
' ',0.0027,0.57,$
' ',0.0041,0.58,$
' ',0.0058,0.59,$
' ',0.0080,0.60,$
' ',0.0106,0.61,$
' ',0.0138,0.62,$
END
-RUN

JOIN 
BLANK WITH SEATS IN CAR TO BLANK IN LOOK AS J1
END
DEFINE FILE CAR
BLANK/A1 WITH SEATS = ' ';
LU_HERE/P6.4  = SEATS/400;
END
TABLE FILE CAR
SUM 
MIN.LU_HERE MAX.LU_FIELD MAX.RT_FIELD
BY COUNTRY BY SEATS
WHERE (LU_FIELD LE LU_HERE) ;
END

...
IF FIELDNAME GE -0.0138 AND FIELDNAME LT -0.0106 THEN 0.38 ELSE
IF FIELDNAME GE -0.0106 AND FIELDNAME LT -0.0080 THEN 0.39 ELSE
IF FIELDNAME GE -0.0080 AND FIELDNAME LT -0.0058 THEN 0.40 ELSE
IF FIELDNAME GE -0.0058 AND FIELDNAME LT -0.0041 THEN 0.41 ELSE
IF FIELDNAME GE -0.0041 AND FIELDNAME LT -0.0027 THEN 0.42 ELSE
IF FIELDNAME GE -0.0027 AND FIELDNAME LT -0.0017 THEN 0.43 ELSE
...