There is a behavior CASE
that has always produced a certain doubt in me. Normally if I see this code:
case when id = 1 then 1 else '' end
I usually change it to something like this:
case when id = 1 then 1 else 0 end
O well
case when id = 1 then 1 else NULL end
That is to say, I am generating a column that, a priori, would have a numerical value, so it does not seem consistent in ELSE
returning a string, even though it is a "blank", so it seems more consistent to me to return a numerical value or in any case a NULL
.
However, this code is fully functional, and the blank returned is somehow coerced to a 0.
select id,
case when id = 1 then 1 else '' end,
case when id = 2 then 1 else '' end
from (select 1 as id union
select 2
) T
+---+---+---+
| 1 | 1 | 0 |
+---+---+---+
| 2 | 0 | 1 |
+---+---+---+
However if instead of a blank ''
string we return another string:
select id,
case when id = 1 then 1 else 'no' end,
case when id = 2 then 1 else 'no' end
from (select 1 as id union
select 2
) T
Msg 245, Level 16, State 1, Line 73
Conversion failed when converting the varchar value 'no' to data type int.
What is this behavior due to? Is this documented somewhere?
According to the documentation of the
CASE
:That is, the data type with the highest precedence is returned (see link) of any of the values returned through the
when
or theelse
. In this example, the highest value would be an integer, so the string returned byelse
is implicitly converted to an integer. And blank strings (''
) are at leastsql-server
converted to the numeric value0
, something that may be debatable and certainly a matter for another question.This is a very particular behavior of
sql-server
, in other engines the results are different:ORA-00932: inconsistent datatypes: expected NUMBER got CHAR
Query Error: error: invalid input syntax for integer: ""