In our previous blog post Different data types in case expression, we have explored the behavior of CASE expression in case of different data types in “THEN” and “ELSE” part. Now in this post “Mutable logical condition in CASE expression”, we are going to unfold the behavior of CASE expression when we have a non deterministic function in logical expression (WHEN part).
Mutable logical condition in CASE expression
Have a look on the below query:
--Query 1 SELECT CASE ABS(CHECKSUM(NEWID())) % 3 WHEN 0 THEN 'Case 0' WHEN 1 THEN 'Case 1' WHEN 2 THEN 'Case 2' END
In above query, logical expression “ABS(CHECKSUM(NEWID())) % 3” of CASE expression, has a non deterministic function NEWID() wrapped inside a CHECKSUM function to generate some random number on fly. Going further the CHECKSUM value has also wrapped inside a ABS function which insures a positive number. Finally there is a Mod operator with value 3 which narrows down the output result to 0 or 1 or 2 only. To catch this output, inside the WHEN part of CASE expression, we have three switches 0, 1 and 2 respectively to return some demo value.
If you execute the above code repeatedly for 6 – 7 times (may vary a bit), you may get unexpected “NULL” value in the output. As we have all possible conditions in WHEN part to catch the output of logical condition, why we are getting this unexpected NULL value? Its just because of the nature of execution of CASE expression. Above query (Query 1) is equivalent to the below query (Query 2):
--Query 2 SELECT CASE WHEN ABS(CHECKSUM(NEWID())) % 3 = 0 THEN 'Case 0' WHEN ABS(CHECKSUM(NEWID())) % 3 = 1 THEN 'Case 1' WHEN ABS(CHECKSUM(NEWID())) % 3 = 2 THEN 'Case 2' END
Logical condition inside CASE expression executes for each WHEN part. Even we have used the logical expression “ABS(CHECKSUM(NEWID())) % 3” once in Query 1, it will execute for each WHEN part (3 times in our case).
When first WHEN clause (WHEN 0) calls the logical condition, it might have got 1 or 2, when second WHEN clause (WHEN 1) calls, it might have got 0 or 2. And finally when third WHEN clause (WHEN 2) calls, it might have got 0 or 1 and all WHEN parts fails to catch. CASE expression always has an ELSE part even we don’t put it explicitly there and here we are getting the NULL from this ELSE part.
|Switch Case||Result received from logical condition||Action|
|WHEN 0||Got 1 or 2||By passed|
|WHEN 1||Got 0 or 2||By passed|
|WHEN 2||Got 0 or 1||By passed|
|ELSE||Always there even not written explicitly||Caught and returned NULL|
Handle mutable logical condition in CASE expression
To handle such kind of situations, simply use derived table to fix the output of logical condition first, then from there, we can create switch cases to catch the output as below:
--Query 3 SELECT CASE WHEN DT.Col = 0 THEN 'Case 0' WHEN DT.Col = 1 THEN 'Case 1' WHEN DT.Col = 2THEN 'Case 2' END FROM (SELECT ABS(CHECKSUM(NEWID())) % 3 AS Col) DT
In above query (Query 3), we have fixed the output in a derived table first and then written switch cases from there. In case of Query 3 it will never output the unexpected NULL value.
I hope you have enjoyed this post and thanks for your reading. I would also like to ask you to share your method in comment section to handle this scenario.
Do post your comments and suggestions, rate this post and most important, please keep sharing.