sql query case statement

Question

Hi,
I have select query with case statement with 35 when conditions for one of the columns. Some one was suggesting to use CTE at the top to take care of it and left outer join main query with CTE to make it clean.
Ex:

SELECT t1.column1, t2.column2,
case
when t1.colx = 'xxx' and t1.coly = 'yyyy' then 'deposit'
when ..........
when.......
35 when statemements
END as description,
t1. column 4
FROM table1 t1
inner join table2 ts

How can make my sql query clean moving case statement to it's own section.
Also, this same logic is used in another 2 stored procedures.

Thanks

Answer 1

If you don't need to reference that case statement again in the query, then you should be OK with the current code. If you do need to reference this new column, then you may do as a separate CTE at the top

;with cte as (select *, case ... as ...
from ...
)

select * from cte LEFT JOIN more tables
where NewColumn = 'Something'

Answer 2

Hi @Spunny
Just to be clear, does any columns in table 2 appears in conditions of those 35 when statements?
If not ,then you can put the 35 case statement inside one CTE, and join the CTE with table2 outside.
Like this:

;WITH CTE AS  
(  
 SELECT column1  
       ,case  
        when colx = 'xxx' and coly = 'yyyy' then 'deposit'  
        when .......... END as description  
	   ,column2  
 FROM table1  
)  
SELECT   
FROM CTE C INNER JOIN table2 T2 ON C.col3=T2.col4  

If yes, then there is no need to use CTE, just current query is ok.

use CTE at the top to take care of it and left outer join main query with CTE to make it clean

Doing this might make the query look clean, but may result in higher query costs. Of course this is just my guess since you didn't provide enough information.

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Hello @Spunny .

I would suggest the following

Create your CTE for table1:

;WITH caseCTE("enter you column list here")  
AS  
(  
SELECT t1.col1, t2.col2,  
"your case statement here",  
t1.col4  
FROM table1 t1  
)  

Then you can do your join with the CTE

select "fields" from caseCTE LEFT JOIN table 2 t2 on ...  

I do hope this helps