I can't get a conditional Join to work. It Joins all Rows basically a Cartesian product.

Here is my Join:

======================================

SET KEEPDEFINES = ON

-RUN

JOIN LEFT_OUTER

FILE RNA4_BOOKING_W_ALIAS AT RNA4_BOOKING_W_ALIAS.Alias_Used WITH Alias_Used
TO MULTIPLE
FILE RNA5_TABLEAU_ORG_TABLE AT RNA5_TABLEAU_ORG_TABLE.PERSON_NAME AS JOINCSR

WHERE ( PERSON_NAME EQ Alias_Used ) ;
WHERE ( ROLE_1 EQ 'CSR' ) ;

END

=========================================

Any Ideas?

This message has been edited. Last edited by: FP Mod Chuck,

What version of 8 are you running?

8105m

FILENAME=RNA4_BOOKING_W_ALIAS, SUFFIX=XFOC , $
SEGMENT=SEG01, SEGTYPE=S1, $
FIELDNAME=FOCLIST, ALIAS=E00, USAGE=I5, $
FIELDNAME=BK_FLAG, ALIAS=E01, USAGE=A1,
TITLE='BK_FLAG', $
FIELDNAME=REGN_ALPH, ALIAS=E02, USAGE=A3,
TITLE='REGN ALPH', $
FIELDNAME=COUNTRY_CD, ALIAS=E03, USAGE=A2,
TITLE='COUNTRY_CD', $
FIELDNAME=REGION, ALIAS=E04, USAGE=A40V,
TITLE='REGION', $
FIELDNAME=PFCTR_ABRV, ALIAS=E05, USAGE=A40V,
TITLE='PFCTR_ABRV', $
FIELDNAME=SSL, ALIAS=E06, USAGE=A40V,
TITLE='SSL', $
FIELDNAME=PC_SUBSLINE, ALIAS=E07, USAGE=A40V,
TITLE='PC_SUBSLINE', $
FIELDNAME=PC_ORG, ALIAS=E08, USAGE=A40V,
TITLE='PC_ORG', $
FIELDNAME=CUSTOMER, ALIAS=E09, USAGE=A60V,
TITLE='CUSTOMER', $
FIELDNAME=UNIT_NAME, ALIAS=E10, USAGE=A40V,
TITLE='UNIT NAME', $
FIELDNAME=ORDER, ALIAS=E11, USAGE=A40V,
TITLE='ORDER#', $
FIELDNAME=ORDVBAK_ASSIGNED_NAME, ALIAS=E12, USAGE=A30,
TITLE='ORDVBAK_ASSIGNED_NAME', $
FIELDNAME=OALIS_PREFERRED_NAME, ALIAS=E13, USAGE=A30,
TITLE='OALIS_PREFERRED_NAME', $
FIELDNAME=BOOK_DATE, ALIAS=E14, USAGE=A8YYMD,
TITLE='BOOK DATE', $
FIELDNAME=SHIP_DATE, ALIAS=E15, USAGE=A8YYMD,
TITLE='SHIP DATE', $
FIELDNAME=CONTRACT, ALIAS=E16, USAGE=A40V,
TITLE='CONTRACT', $
FIELDNAME=CONTVBAK_ASSIGNED_NAME, ALIAS=E17, USAGE=A30,
TITLE='CONTVBAK_ASSIGNED_NAME', $
FIELDNAME=CALIS_PREFERRED_NAME, ALIAS=E18, USAGE=A30,
TITLE='CALIS_PREFERRED_NAME', $
FIELDNAME=PROGRAM, ALIAS=E19, USAGE=A40V,
TITLE='PROGRAM', $
FIELDNAME=ID_ENTRY, ALIAS=E20, USAGE=A40V,
TITLE='ID_ENTRY', $
FIELDNAME=CSR_PREFERRED_NAME, ALIAS=E21, USAGE=A30,
TITLE='CSR_PREFERRED_NAME', $
FIELDNAME=ENTRY, ALIAS=E22, USAGE=A40V,
TITLE='ENTRY', $
FIELDNAME=CSRN_PREFERRED_NAME, ALIAS=E23, USAGE=A30,
TITLE='CSRN_PREFERRED_NAME', $
FIELDNAME=FINANCIAL, ALIAS=E24, USAGE=A40V,
TITLE='FINANCIAL', $
FIELDNAME=EVENT, ALIAS=E25, USAGE=A40V,
TITLE='EVENT NUM', $
FIELDNAME=PRCTR, ALIAS=E26, USAGE=A40V,
TITLE='PRCTR', $
FIELDNAME=PC, ALIAS=E27, USAGE=A40V,
TITLE='PC', $
FIELDNAME=PO, ALIAS=E28, USAGE=A40V,
TITLE='PO', $
FIELDNAME=CM_OE, ALIAS=E29, USAGE=P15.2,
TITLE='CM OE', $
FIELDNAME=YTD_OE, ALIAS=E30, USAGE=P15.2,
TITLE='YTD OE', $
FIELDNAME=YTD_MARGIN, ALIAS=E31, USAGE=P15.2,
TITLE='YTD MARGIN', $
FIELDNAME=YTD_BKBL, ALIAS=E32, USAGE=P15.2,
TITLE='YTD BKBL', $
FIELDNAME=YTD_BLBLM, ALIAS=E33, USAGE=P15.2,
TITLE='YTD BLBLM', $
FIELDNAME=BIIL_PLUS_1, ALIAS=E34, USAGE=P15.2,
TITLE='BIIL+1', $
FIELDNAME=BIIL_PLUS_2, ALIAS=E35, USAGE=P15.2,
TITLE='BILL+2+', $
FIELDNAME=AEIC, ALIAS=E36, USAGE=A40V,
TITLE='AEIC', $
FIELDNAME=TURBMNF, ALIAS=E37, USAGE=A40V,
TITLE='TURBMNF', $
FIELDNAME=TURB_FRAME, ALIAS=E38, USAGE=A40V,
TITLE='TURB FRAME', $
FIELDNAME=BK_FLAG, ALIAS=E39, USAGE=A1,
TITLE='BK_FLAG', $
FIELDNAME=O_T, ALIAS=E40, USAGE=A40V,
TITLE='O T', $
FIELDNAME=SOURCE, ALIAS=E41, USAGE=A40V,
TITLE='SOURCE', $
FIELDNAME=SRC_PREFERRED_NAME, ALIAS=E42, USAGE=A30,
TITLE='SRC_PREFERRED_NAME', $
FIELDNAME=CSR_EXC_30, ALIAS=E43, USAGE=A30, $
FIELDNAME=EXCAL_PREFERRED_NAME, ALIAS=E44, USAGE=A30,
TITLE='EXCAL_PREFERRED_NAME', $
FIELDNAME=Alias_Used, ALIAS=E45, USAGE=A30, FIELDTYPE=I, $
FIELDNAME=Alias_Source, ALIAS=E46, USAGE=A30, $

FILENAME=RNA5_TABLEAU_ORG_TABLE, SUFFIX=XFOC , $
SEGMENT=SEG01, SEGTYPE=S6, $
FIELDNAME=DIRECTOR_NAME, ALIAS=E01, USAGE=A30, FIELDTYPE=I, $
FIELDNAME=MANAGER_NAME, ALIAS=E02, USAGE=A30, FIELDTYPE=I, $
FIELDNAME=PERSON_NAME, ALIAS=E03, USAGE=A30, FIELDTYPE=I, $
FIELDNAME=START_DATE, ALIAS=E04, USAGE=A8YYMD, FIELDTYPE=I, $
FIELDNAME=END_DATE, ALIAS=E05, USAGE=A8YYMD, FIELDTYPE=I, $
FIELDNAME=FOCLIST, ALIAS=E00, USAGE=I5, $
FIELDNAME=ORG_1, ALIAS=E06, USAGE=A10, FIELDTYPE=I, $
FIELDNAME=ORG_2, ALIAS=E07, USAGE=A10, FIELDTYPE=I, $
FIELDNAME=ORG_3, ALIAS=E08, USAGE=A10, FIELDTYPE=I, $
FIELDNAME=ROLE_1, ALIAS=E09, USAGE=A10, FIELDTYPE=I, $
FIELDNAME=ROLE_2, ALIAS=E10, USAGE=A10, FIELDTYPE=I, $
FIELDNAME=ROLE_3, ALIAS=E11, USAGE=A10, FIELDTYPE=I, $

From the documentation :

quote:

How to Create a Conditional JOIN

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:

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.

Sample:

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