I'm writing an SQL Query, where a few of the columns returned need to be calculated depending on quite a lot of conditions.
I'm currently using nested case statements, but its getting messy. Is there a better (more organised and/or readable) way?
(I am using Microsoft SQL Server, 2005)
A simplified example:
SELECT col1, col2, col3, CASE WHEN condition THEN CASE WHEN condition1 THEN CASE WHEN condition2 THEN calculation1 ELSE calculation2 END ELSE CASE WHEN condition2 THEN calculation3 ELSE calculation4 END END ELSE CASE WHEN condition1 THEN CASE WHEN condition2 THEN calculation5 ELSE calculation6 END ELSE CASE WHEN condition2 THEN calculation7 ELSE calculation8 END END END AS 'calculatedcol1', col4, col5 -- etc FROM table 9 Answers
You could try some sort of COALESCE trick, eg:
SELECT COALESCE( CASE WHEN condition1 THEN calculation1 ELSE NULL END, CASE WHEN condition2 THEN calculation2 ELSE NULL END, etc... )7
Wrap all those cases into one.
SELECT col1, col2, col3, CASE WHEN condition1 THEN calculation1 WHEN condition2 THEN calculation2 WHEN condition3 THEN calculation3 WHEN condition4 THEN calculation4 WHEN condition5 THEN calculation5 ELSE NULL END AS 'calculatedcol1', col4, col5 -- etc FROM table You can combine multiple conditions to avoid the situation:
CASE WHEN condition1 = true AND condition2 = true THEN calculation1 WHEN condition1 = true AND condition2 = false ELSE 'what so ever' END, I personally do it this way, keeping the embedded CASE expressions confined. I'd also put comments in to explain what is going on. If it is too complex, break it out into function.
SELECT col1, col2, col3, CASE WHEN condition THEN CASE WHEN condition1 THEN CASE WHEN condition2 THEN calculation1 ELSE calculation2 END ELSE CASE WHEN condition2 THEN calculation3 ELSE calculation4 END END ELSE CASE WHEN condition1 THEN CASE WHEN condition2 THEN calculation5 ELSE calculation6 END ELSE CASE WHEN condition2 THEN calculation7 ELSE calculation8 END END AS 'calculatedcol1', col4, col5 -- etc FROM table Here's a simple solution to the nested "Complex" case statment: --Nested Case Complex Expression
select datediff(dd,Invdate,'2009/01/31')+1 as DaysOld, case when datediff(dd,Invdate,'2009/01/31')+1 >150 then 6 else case when datediff(dd,Invdate,'2009/01/31')+1 >120 then 5 else case when datediff(dd,Invdate,'2009/01/31')+1 >90 then 4 else case when datediff(dd,Invdate,'2009/01/31')+1 >60 then 3 else case when datediff(dd,Invdate,'2009/01/31')+1 >30 then 2 else case when datediff(dd,Invdate,'2009/01/31')+1 >30 then 1 end end end end end end as Bucket from rm20090131atb Just make sure you have an end statement for every case statement
2We can combine multiple conditions together to reduce the performance overhead.
Let there are three variables a b c on which we want to perform cases. We can do this as below:
CASE WHEN a = 1 AND b = 1 AND c = 1 THEN '1' WHEN a = 0 AND b = 0 AND c = 1 THEN '0' ELSE '0' END, a user-defined function may server better, at least to hide the logic - esp. if you need to do this in more than one query
I went through this and found all the answers super cool, however wants to add to answer given by @deejers
SELECT col1, col2, col3, CASE WHEN condition1 THEN calculation1 WHEN condition2 THEN calculation2 WHEN condition3 THEN calculation3 WHEN condition4 THEN calculation4 WHEN condition5 THEN calculation5 END AS 'calculatedcol1', col4, col5 -- etc FROM table you can make ELSE optional as its not mandatory, it is very helpful in many scenarios.
This example might help you, the picture shows how SQL case statement will look like when there are if and more than one inner if loops
ncG1vNJzZmirpJawrLvVnqmfpJ%2Bse6S7zGiorp2jqbawutJobGltZ2mEcK7ErKtmr5GuerW7jJ2mZqaVqMGmsIycmKydXajBosDEppynrF2hvKi1wmagp2Wjprluv8SrrZ6q