Backward Use of CASE in SQL
I was updating an old app to use SQL on a new SQL Server database rather than an older Access database. As any database programmer is aware, there are irritating inconsistencies between Access SQL and SQL Server's T-SQL, such as absence of the IIF() and Trim() functions in T-SQL.
So I was going through the SQL and making these changes. I came across a very ugly IIF() function similar to this:
-
IIF(phLead=True,'LEAD',
-
IIF(phFax=True,'FAX',
-
IIF(phVVM=True,'VVM', 'DEPT')))
Take that statement and add even more boolean fields. Clearly, the intent was to have a calculated field that had a friendly name based on which boolean field was true.
So, how can this be easily converted into a CASE statement? As it stands, CASE statements are actually more verbose than the IIF() function, and the IIF() function above is already hard to read and verbose as it is.
A direct code conversion would be something like this:
-
CASE phLead WHEN 1 THEN 'LEAD'
-
ELSE CASE phFax WHEN 1 THEN 'FAX'
-
ELSE CASE phVVM WHEN 1 THEN 'VVM'
-
ELSE 'DEPT' END END END
Ugly, ugly, ugly. The cool thing about CASE that differs from IIF() is that you can attack it from the other direction. Instead of saying, "Is phLead true? Is phFax true? Is phVVM true?", you can instead say, "Okay, who's true? phLead? phFax? phVVM?" It sounds negligible, but the code is shorter and easier to read:
-
CASE 1 WHEN phLead THEN 'LEAD'
-
WHEN phFax THEN 'FAX'
-
WHEN phVVM then 'VVM'
-
ELSE 'DEPT' END
Ahh, much better.
November 9th, 2007 at 8:41 am
Technically you don’t even need the 1 in your last example. There are two different flavors of CASE (as you’ve illustrated) - a simple case and a searched case. Here’s what the docs show:
Simple CASE function:
CASE input_expression
WHEN when_expression THEN result_expression
[ …n ]
[
ELSE else_result_expression
]
END
Searched CASE function:
CASE
WHEN Boolean_expression THEN result_expression
[ …n ]
[
ELSE else_result_expression
]
END
November 9th, 2007 at 8:59 am
Thanks! I never realized this alternate syntax existed until a need like this called for it.
November 9th, 2007 at 2:41 pm
Funny - there was a long time that I didn’t know the “simple” method existed a while back!
November 9th, 2007 at 2:56 pm
Lol! When I first read about CASE statements in SQL way back when, it resonated with my understanding of SWITCH..CASE statements in C. Until this situation, I never even considered that the SQL equivalent would work any other way.