Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Idyana
Yak Posting Veteran
96 Posts |
 Posted - 2012-02-25 : 03:39:10
|
My statment as following,declare @t1 table(idx int)insert into @t1 values(-2147483450)insert into @t1 values(0)insert into @t1 values(1)select case when idx=0 then '-'else idx end as idxfrom @t1 My result wasidx-214748345001 why 0 not display as - ?I'm very stuck |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-02-25 : 03:46:59
|
All the values returned from a CASE expression have to be of the same type. If they are not, SQL will try to convert them to a single type. In this case, it tries to convert the '-' to an int, which turns out to be zero. (Try SELECT CAST('-' AS INT) and you will see what I mean. Or, try changing your select statement to ...case when idx=0 then 'ABCD' and you will see it throws an error)If you are trying to display a dash when the value is zero, most people suggest that it be done on the client side (such as presentation layer). Now, if you MUST do this in SQL, force the return data type to varchar like this:select case when idx=0 then '-'else CAST(idx AS VARCHAR(32)) end as idxfrom @t1 |
 |
|
|
Idyana
Yak Posting Veteran
96 Posts |
Posted - 2012-02-25 : 03:54:32
|
| tq mam |
|
|
|
|
|
|
need help on case statement