August 20, 2008 at 12:52 am
AOA
i have a table test with data type int. There is only one column val1 in test. values in val1 are as follow
1
2
3
1000
4000
2000
3000
...
when i run the following query a difference occurs in result
select val1,right(val1,3),val1 =
case
when Len(val1)>1 then right(val1,3)
else val1
end
from test
result is some thing like
111
111
222
333
10000000
10000000
10000000
10000000
10000000
10000000
10000000
10000000
10000000
10000000
10000000
10000000
10000000
The problem is that in third column generated through case statement why it is displaying one zero instead of three as in second column.
please point out the reason and solution.
Regards
DBDigger Microsoft Data Platform Consultancy.
August 20, 2008 at 1:06 am
hi,
here is your solution....
Declare your VAL1 column with varchar datatype ypu will get your solution....
In your case ity is showing 0 instead of 000 because as integer 000 is treated as 0
BUT i m still confused why this is happening in case syntax..
But for you relief i have provided the solution
Use VARCHAR instead of INT
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
August 20, 2008 at 1:11 am
The reason is that right() function returns a string value where as case returns the highest precedence type from the set of types in when or else clause. Which, in this situation is INT and as per the data type precedings followed by SS2K5, INT falls before varchar.
So the return that you are getting in third column is int and in second column is string. If you need the same output in third column as well, simply cast the else clause to varchar:
select val1,right(val1,3),val1 =
case
when Len(val1)>1 then right(val1,3)
else cast(val1 as varchar)
end
from test
August 20, 2008 at 1:15 am
check my query, I haven't casted the whole case but only the else part which was returning the INT value.:D
August 20, 2008 at 1:16 am
Hi Grasshoper,
Really your reply is APPRECIATED...
can you look into my issuse on below link
http://www.sqlservercentral.com/Forums/Topic555531-146-1.aspx
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply