CODEWITHSUNDEEP
sqlsql-serversql-server-2008casting
amajors
amajors

Reputation: 13

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?

Upvotes: 0

Views: 12500

Answers (1)

Felix Pamittan
Felix Pamittan

Reputation: 31879

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;

Upvotes: 4

Related Questions