Focal Point Banner


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 myibi@ibi.com and provide your corporate email address, company, and name.


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

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[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: 117 | Registered: May 28, 2015Report 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.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6349 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report 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: 117 | Registered: May 28, 2015Report 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.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6349 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Platinum Member
posted Hide Post
That did it, it cleared up the multiples and the where clause worked.

Thanks


WF8
Windows
 
Posts: 117 | Registered: May 28, 2015Report This Post
Expert
posted Hide Post
Great news.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6349 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

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

Copyright © 1996-2020 Information Builders