Universe design - conditional join

[jaburke]

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!

 

[skrandel]

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

 

[blom0344]

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

 

[BrianTyler]

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
;

 

[PAULSGS]

I have used decode (Oracle) to achieve this

 

[jaburke]

Thanks for the tips. I ended up using an or statement in my join and that seems to have worked. Thanks!