I have a query I'm working through rewriting - it has a huge bunch of case
statements in the select
list that all basically look like this:
select
Column1 =
case when (Something = 2) and (SomethingElse is null) and (AnotherThing in (1, 2))
then 1
else 0
end,
Column2 =
case when (Something = 3) and (SomethingElse is not null) and (AnotherThing in (2, 3))
then 1
else 0
end,
...
With particular note to the "when <expressions> then 1 else 0
" logic common across all of them.
It seems really bloated and wordy, a heavy-handed use of the case
statement - my programming brain is telling me this could be achieved much more succinctly with some basic binary &
ing logic, removing the case
entirely:
select
Column1 = (Something = 2) & (SomethingElse is null) & (AnotherThing in (1, 2)),
Column2 = (Something = 3) & (SomethingElse is not null) & (AnotherThing in (2, 3)),
...
Unfortunately whilst SQL Server is able to handle something like select 1 & 0
(returns 0
), it seems to choke on parsing expressions in the select list (Incorrect syntax near '='
) - is it possible to do something like this? Some kind of evaluation function, maybe?
then 1 else 0 end
phrase to a single line instead of three to make it look more elegant. What you propose could be done if SQL-Server had a boolean datatype - but it hasn't. – ypercubeᵀᴹ Aug 27 '14 at 10:20if (a & b & c) { d = true; } else { d = false; }
in a programming language compared to justd = (a & b & c);
(I know comparing SQL to programming languages is a suspect practice at best, but this seems fairly baseline stuff). I wonder why SQL Server lacks a boolean data type? – Kai Aug 27 '14 at 10:34