![]() |
Best way to do nested case statement logic in SQL Server - Printable Version +- 0Day Forums (https://0day.red) +-- Forum: Coding (https://0day.red/Forum-Coding) +--- Forum: Database (https://0day.red/Forum-Database) +---- Forum: Microsoft SQL Server (https://0day.red/Forum-Microsoft-SQL-Server) +---- Thread: Best way to do nested case statement logic in SQL Server (/Thread-Best-way-to-do-nested-case-statement-logic-in-SQL-Server) Pages:
1
2
|
Best way to do nested case statement logic in SQL Server - tapia426 - 07-31-2023 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 RE: Best way to do nested case statement logic in SQL Server - broadbills557140 - 07-31-2023 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 RE: Best way to do nested case statement logic in SQL Server - fernandeaaev - 07-31-2023 You could try some sort of COALESCE trick, eg: <pre> SELECT COALESCE( CASE WHEN condition1 THEN calculation1 ELSE NULL END, CASE WHEN condition2 THEN calculation2 ELSE NULL END, etc... ) </pre> RE: Best way to do nested case statement logic in SQL Server - superheroic970839 - 07-31-2023 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 RE: Best way to do nested case statement logic in SQL Server - misdecidemini - 07-31-2023 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 RE: Best way to do nested case statement logic in SQL Server - stenothermy409067 - 07-31-2023 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, RE: Best way to do nested case statement logic in SQL Server - setwise370890 - 07-31-2023 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 RE: Best way to do nested case statement logic in SQL Server - Dralhambra2 - 07-31-2023 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. RE: Best way to do nested case statement logic in SQL Server - merilynyyfzbvw - 07-31-2023 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 [![enter image description here][1]][1] [1]: RE: Best way to do nested case statement logic in SQL Server - quathlamba126 - 07-31-2023 You can combine multiple conditions to avoid the situation: CASE WHEN condition1 = true AND condition2 = true THEN calculation1 WHEN condition1 = true AND condition2 = false THEN calculation2 ELSE 'what so ever' END, |