sql server - CAST and CASE in SQL SELECT statement -


i'm trying return string when conditions true, i'm running data type issue... li.num_seats_pur , li.num_seats_ret both smallint data types...

here's i'm stuck:

select case   when (li.num_seats_ret = li.num_seats_pur)     'ret'   else (li.num_seats_pur - li.num_seats_ret) end 'seats'  t_lineitem li; 

i understand 'ret' not smallint, every combination of cast use here still causing error. ideas?

when using case expression, if return values have different data types, converted 1 higher data type precedence. , since smallint has higher precedence varchar, return value of else part, 'ret' gets converted smallint. proceed conversion error:

conversion failed when converting varchar value 'ret' data type smallint.

in order achieve desired result, need cast else part varchar:

select     case         when (li.num_seats_ret = li.num_seats_pur)             'ret'         else              cast((li.num_seats_pur - li.num_seats_ret) varchar(10))     end 'seats' t_lineitem li; 

Comments

Popular posts from this blog

java - nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet Hibernate+SpringMVC -

sql - Postgresql tables exists, but getting "relation does not exist" when querying -

asp.net mvc - breakpoint on javascript in CSHTML? -