All Forums Database
drmkd17 54 posts Joined 10/12
28 Feb 2016
Alternative for Multiple Left outer join
Select coalesce (t1.col3,p1.col3) as target_Column1,
coalesce (t2.col3,p2.col3) as target_Column2,
coalesce (t3.col3,p3.col3) as target_Column3,
...
coalesce (t25.col3,p25.col3) as target_Column3,
from 
d1.g1_view a
join 
d1.g1_metadate b
on 
b.cd='some_cd'
left outer join
d1.val_table t1
on 
t1.val_cd = a.some_ind
and t1.schm_Cd ='abc'
left outer join 
d1.schm_tab p1
on 
p1.schm_cd ='abc'

left outer join 
d1.val_table t2
t1.val_cd = a.some_other_ind
and t1.schm_Cd ='pqr'
d1.schm_tab p2
on 
p1.schm_cd ='pqr'
...
left outer join 
d1.val_table t25
t1.val_cd = a.some_other_other_ind
and t1.schm_Cd ='xyz'
d1.schm_tab p25
on 
p1.schm_cd ='xyz'

Hi All,
 
I have a view running for a very long time. Can anybody suggest me alternative for left outer joins to val table and the schm_tab. Thanks in advance.
 
Thanks,
DrmKd
 
 

Adeel Chaudhry 773 posts Joined 04/08
06 Mar 2016

In one line, there is no alternative to LEFT OUTER JOIN.
Regarding fixing performance .... How are the tables designed? What the joining columns? What is the PI? .... search forum and you will get many scenarios of performance issues.
There is no magic to performant queries. :)

-- If you are stuck at something .... consider it an opportunity to think anew.

You must sign in to leave a comment.