Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Conditional Join
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Conditional Join
 Login/Join
 
Platinum Member
posted
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,


WF8
Windows
 
Posts: 110 | Registered: May 28, 2015Reply With QuoteReport This Post
Expert
posted Hide Post
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...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.04OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6015 | Location: Land of the Darug people, Terra Australis Incognita | Registered: October 31, 2006Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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
 
Posts: 110 | Registered: May 28, 2015Reply With QuoteReport This Post
Expert
posted Hide Post
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...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.04OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6015 | Location: Land of the Darug people, Terra Australis Incognita | Registered: October 31, 2006Reply With QuoteReport This Post
Platinum Member
posted Hide Post
That did it, it cleared up the multiples and the where clause worked.

Thanks


WF8
Windows
 
Posts: 110 | Registered: May 28, 2015Reply With QuoteReport This Post
Expert
posted Hide Post
Great news.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.04OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6015 | Location: Land of the Darug people, Terra Australis Incognita | Registered: October 31, 2006Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Conditional Join

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.