Best way to do nested case statement logic in SQL Server
SqlSql ServerSql Server-2005SelectNestedSql Problem Overview
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
Sql Solutions
Solution 1 - Sql
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... )
Solution 2 - Sql
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
Solution 3 - Sql
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,
Solution 4 - Sql
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
Solution 5 - Sql
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
Solution 6 - Sql
We 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,
Solution 7 - Sql
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
Solution 8 - Sql
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.
Solution 9 - Sql
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
