amajors amajors - 7 months ago 9
SQL Question

CAST and CASE in SQL SELECT statement

I'm trying to return a string when certain conditions are true, but the I'm running into a data type issue... LI.num_seats_pur and LI.num_seats_ret are both smallint data types...

Here's where I'm stuck:

SELECT
CASE
WHEN (LI.num_seats_ret = LI.num_seats_pur)
THEN 'RET'
ELSE (LI.num_seats_pur - LI.num_seats_ret)
END as 'Seats'

FROM T_LINEITEM LI;


I understand that 'RET' is obviously not a smallint, but every combination of CAST I use here is still causing an error. Any ideas?

Answer

When using a CASE expression, if the return values have different data types, they will be converted to the one with the higher data type precedence. And since SMALLINT has a higher precedence than VARCHAR, the return value of the ELSE part, 'RET' gets converted to SMALLINT. This will then proceed to a conversion error:

Conversion failed when converting the varchar value 'RET' to data type smallint.

In order to achieve the desired result, you need to CAST the ELSE part to VARCHAR:

SELECT
    CASE
        WHEN (LI.num_seats_ret = LI.num_seats_pur)
            THEN 'RET'
        ELSE 
            CAST((LI.num_seats_pur - LI.num_seats_ret) AS VARCHAR(10))
    END AS 'Seats'
FROM T_LINEITEM LI;