Rewrite Case when statements

Database
Enthusiast

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

1 REPLY
Teradata Employee

Re: Rewrite Case when statements

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.