I am trying a conditional join that works but when I try to report out a field from the cross referenced table I get an error message. Below is the code and it works but when I uncomment the line to print the SGRSPRT_ACTC_CODE I get the following error message.

JOIN LEFT_OUTER
FILE spriden AT SPRIDEN_PIDM TAG J1L TO UNIQUE
FILE sgrsprt AT SGRSPRT_PIDM TAG J1R AS J1
WHERE J1R.sgrsprt EQ J1L.spriden;
WHERE J1R.SGRSPRT_ACTC_CODE EQ 'WVB' AND J1R.SGRSPRT_TERM_CODE EQ '201940';
END

TABLE FILE SPRIDEN
PRINT
SPRIDEN_ID
-*SGRSPRT_ACTC_CODE
BY SPRIDEN_PIDM
WHERE SPRIDEN_CHANGE_IND IS MISSING AND SPRIDEN_PIDM EQ 289955 OR 290039;
END



 0 ERROR AT OR NEAR LINE      6  IN PROCEDURE ConditionalJOINFOCEXEC *
 (FOC258) FIELDNAME OR COMPUTATIONAL ELEMENT NOT RECOGNIZED: J1R.sgrsprt
 0 ERROR AT OR NEAR LINE      4  IN PROCEDURE conditionaljoin
 (FOC258) FIELDNAME OR COMPUTATIONAL ELEMENT NOT RECOGNIZED: spriden
 0 ERROR AT OR NEAR LINE     12  IN PROCEDURE conditionaljoin
 (FOC003) THE FIELDNAME IS NOT RECOGNIZED: SGRSPRT_ACTC_CODE
 (FOC009) INCOMPLETE REQUEST STATEMENT
 BYPASSING TO END OF COMMAND
  

This message has been edited. Last edited by: Trudy,

The syntax for a conditional join is:

JOIN ...
FILE {Master Name} AT {Column Name} ...
FILE {Master Name} AT {Column Name} ...
WHERE ...
END

What is sgrsprt and spriden ?

If they are Master Files, then what is "WHERE J1R.sgrsprt EQ J1L.spriden;" supposed to do ?

I was following an example I from IBI's Tech Support page and that was what they had.
When I comment out that line and run it I get the two ID's showing up but now I get each of them occuring 16x's and they both have a WVB value in the SGRSPRT_ACTC_CODE field and only the one ID should have that. What I expect to see is the following

  
SPRIDEN_PIDM SPRIDEN_ID SGRSPRT_ACTC_CODE
289955 	10251966 	WVB
290039  10252051

Instead I see 16 rows each with 290039 having the value 'WVB' when they do not have that assigned to them in the table, in fact they don't even have a record in the SGRSPRT table. The 16 rows I know the first ID has WVB in her sgrsprt table 16 times for the 16 different term codes but the where clause is supposedly looking at only the one term code. I can't have the SGRSPRT table be the host table because I would lose the second person.

If SPRIDEN_PIDM and SGRSPRT_PIDM are the common keys, then you should have the line:
WHERE J1R.SGRSPRT_PIDM EQ J1L.SPRIDEN_PIDM ;


Also may be useful to turn on SQL tracing to see what is generated.

That did it, it cleared up the multiples and the where clause worked.

Thanks

Great news.