As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at email@example.com and provide your corporate email address, company, and name.
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.
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';
TABLE FILE SPRIDEN
WHERE SPRIDEN_CHANGE_IND IS MISSING AND SPRIDEN_PIDM EQ 289955 OR 290039;
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,
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
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.