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.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

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.


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
 
WF World
Silver Member
posted
Could someone suggest how to achieve this in conditioanl join.

  
SQL code
ENTL_CP.BUS_PROC_CD = B.BUS_PROC_CD
AND
ENTL_CP.BUS_EVENT_CD = COALESCE(B.EXCN_EVNT_CD,ENTL_CP.BUS_EVENT_CD)



What tried is
 
JOIN
 FILE H_ENTLCP AT BUS_PROC_CD TO MULTIPLE FILE H_RQST_TY AT BUS_PROC AS J0
 WHERE BUS_PROC_CD EQ BUS_PROC;
 IF EXCN_EVNT EQ '' THEN BUS_EVENT_CD ELSE EXCN_EVNT;
 WHERE EXCN_EVNT EQ BUS_EVENT_CD;
END


but this is not working for me.

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


WebFOCUS 8.1.03
Windows, Linux All Outputs
 
Posts: 46 | Location: India | Registered: August 18, 2011Report This Post
Kerry
Expert
posted Hide Post
Hi WF World,

Can you please be a little more specific: what is not working for you? Do you receive any error message?

Cheers,

Kerry


Kerry Zhan
Focal Point Moderator
Information Builders, Inc.
 
Posts: 1948 | Location: New York | Registered: November 16, 2004Report This Post
njsden
Virtuoso
posted Hide Post
I don't think your syntax is correct.

How about:

JOIN
 FILE H_ENTLCP AT BUS_PROC_CD TO MULTIPLE FILE H_RQST_TY AT BUS_PROC AS J0
 WHERE (H_ENTLCP.BUS_PROC_CD EQ H_RQST_TY.BUS_PROC)
   AND (H_RQST_TY.EXCN_EVNT_CD EQ MISSING 
    OR  (H_RQST_TY.EXCN_EVNT_CD NE MISSING AND H_ENTLCP.BUS_EVENT_CD EQ H_RQST_TY.EXCN_EVNT_CD)
       )
END



That should achieve the same logical expression you have in SQL -- of course, I'm trying my best to guess which fields belong to which table but you'll get the idea.


This is actually one of those cases where the JOIN tool in Developer Studio works beautifully. You should give it a try and see how nice it is to create the expressions and take care of the syntax.


Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
njsden
Virtuoso
posted Hide Post
Hmmm, looking at your join structure gives me the sensation that you're trying to implement what a native outer join would do.

Have you tried coding your query statement to left_outer join H_ENTLCP to H_RQST_TY on (BUS_PROC_CD = BUS_PROC and BUS_EVENT_CD = EXCN_EVNT_CD)?


Obviously only you know your data, constraints and query requirement but this may be worth trying.


Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
WF World
Silver Member
posted Hide Post
quote:
H_RQST_TY.EXCN_EVNT_CD EQ MISSING


Thank you NJ.

I'll try as you said. But to my surprise when I give Webfocus join it is not recognized on SQL TRACE... anyways I'll give try as you said and I will get back. Smiler


WebFOCUS 8.1.03
Windows, Linux All Outputs
 
Posts: 46 | Location: India | Registered: August 18, 2011Report This Post
WF World
Silver Member
posted Hide Post
Hi Everyone..

As I've Million records to pull so I was suggested that conditional is not a good suggestion to work. So I've created view for the query and created Synonym for the same.

NJ, Your suggestion is well worth to solve the issue.

Thank you NJ..


WebFOCUS 8.1.03
Windows, Linux All Outputs
 
Posts: 46 | Location: India | Registered: August 18, 2011Report 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