All Forums Database
srivigneshkn 21 posts Joined 02/16
12 Apr 2016
Rewrite Case when statements

Hi, 

 

I have a following query with multiple case when statements and i would need to performance tune it.

 

Request your help in re-writing this code.

 

Select 

Case when (Table1.col2=Table2.col2) then Table1.Qty else Null end as AndJoinQty1

Case when (Table1.col3=Table2.col2) then Table1.Qty else Null end as OrJoinQty1

Case when (Table1.col2=Table2.col2) then Table1.Qty else Null end as AndJoinQty2

Case when (Table1.col3=Table2.col2) then Table1.Qty else Null end as OrJoinQty2

Case when (Table1.col2=Table2.col2) then Table1.Qty else Null end as AndJoinQty2

Case when (Table1.col3=Table2.col2) then Table1.Qty else Null end as OrJoinQty3

from Table1 inner join Table2

on Table1.col1=Table2.col1

and (Table1.col2=Table2.col2 

     or Table1.col3=Table2.col2)

and Table1.col4=Table2.col4 

and Table1.col5=Table2.col5

left outer join Table3 on Table1.col6=Table3.col6;

 

 

Essentially the join condition is repeated multiple times in the case statements, would like to know the alternative approach to achieve this instead of repeating the join conditions multiple times within a case statment.

 

 

Thanks & Regards,

Sri

ToddAWalter 316 posts Joined 10/11
13 Apr 2016

The case expressions are the same in the 1,3,5 lines and the 2,4,6 lines. Is this intended? If the desire is to just have a shorthand for the repetition, then the name specified in the AS can just be referenced.
Case when (Table1.col2=Table2.col2) then Table1.Qty else Null end as AndJoinQty1
Case when (Table1.col3=Table2.col2) then Table1.Qty else Null end as OrJoinQty1
AndJoinQty1 as AndJoinQty2
OrJoinQty1 as OrJoinQty2
AndJoinQty1 as AndJoinQty3
OrJoinQty1 as OrJoinQty3
 
If the concern is that the conditions are the same as the conditions in the join, there is no impact to doing that if that is the logic needed to get the desired result.

You must sign in to leave a comment.