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
This question is related to
sql
sql-server
sql-server-2005
select
nested
The answer is
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
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
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,
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,
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
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,
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
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.
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
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
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
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
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
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
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
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
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.
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
Source: Stackoverflow.com