Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Universe design - conditional join

Status
Not open for further replies.

jaburke

Programmer
May 20, 2002
156
US
Hi there...
I am creating a new universe. I have a situation. There are two tables that I am trying to join, but the join is conditional. If status = 'I' then I need to join one way. If status <> 'I' then I need to take a different join path. Any help would be greatly appreciated.

Thanks!
 
Think about it a bit differently. What SQL code could you write that would do this?

It might be possible using a derived table or a view, but I don't think so. You can't have logic in the Where clause.

Steve Krandel
Symantec
 
I was wondering if you cannot ' trick' BO into doing this by using contexts. If multiple paths are available, then create context ' Status = I ' and 2nd one ' Status <> I'
If you then run a report with the proper objects you will be asked to select the context.
This way you may influence the join path..

Ties Blom

 
Using aliases in raw SQL against a DB2 database, I managed to get a result. OK this is a simple query, but you may be able to make the left outer joins do what you want.


select
cu_cust_no
,case when b.ca_ref_no is null then a.ca_addr1 else b.ca_pcode end
from
customer left outer join custaddr A on a.ca_cust_no = cu_cust_no and cu_status >12
left outer join custaddr B on b.ca_cust_no = cu_cust_no and cu_status <=12
fetch first 100 rows only
;
 
Thanks for the tips. I ended up using an or statement in my join and that seems to have worked. Thanks!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top