 |
|||||
|
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. Join the TIBCO Community
Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community. | |||||
Read-Only TopicGo | Search | Notify | Tools |
| [SOLVED] Conditional Join | Login/Join |
| Platinum Member |
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 COMMANDThis message has been edited. Last edited by: Trudy, WF8 Windows | ||
|
| Expert |
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 ? Waz...
| |||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||
| Platinum Member |
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. WF8 Windows | |||
|
| Expert |
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. Waz...
| |||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||
| Platinum Member |
That did it, it cleared up the multiples and the where clause worked. Thanks WF8 Windows | |||
|
| Expert |
Great news. Waz...
| |||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only TopicCopyright © 1996-2020 Information Builders
| View $GS_USERNAME's Public Profile |
| Add $GS_USERNAME to my Ignore List |
| View Recent Posts by $GS_USERNAME |
| Notify me of New Posts by $GS_USERNAME |