January 14, 2009 at 2:23 pm
I've got a char variable (@tbl_num_char) that I'm trying to populate with the value from an int variable (@tbl_num); but where the value for @tbl_num is less than 10, I need @tbl_num_char to display as "01", "02", etc.- not "1", "2", etc.
I've had half my office look over the code below; and we're all stumped as to what the problem is here. If you try running the code, you'll see what I mean.
The 2 PRINT commands at the end are supposed to highlight the difference between the two variable values; but instead they read as the same:
2
2
i was expecting:
2
02
Interestingly, if you replace the middle line in the CASE statement with
when @tbl_num < 9 then '5' + cast((@tbl_num) as char),
the code does produce the desired result:
2
52
so the question is: what's the deal with '0'? I need SQL to treat this as a char value, not a number; and when I try to cast the '0' as char in the statement in question, I still get the same result as when I ran it without CAST.
--
declare @tbl_num int
declare @tbl_num_char char(2)
set @tbl_num = 1
set @tbl_num_char =
case
when @tbl_num = 9 then '10'
when @tbl_num < 9 then '0' + cast((@tbl_num) as char)
when @tbl_num > 9 then @tbl_num
end
print @tbl_num
print @tbl_num_char
--
January 14, 2009 at 2:28 pm
Try cast/convert on @tbl_num in the last line of your case statement. That should do it for you. Case statements go with the usual implicit conversion rules. The reason it works with a 5 is that you're getting back the integer 52.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 14, 2009 at 2:30 pm
You need to make all of the values being returned the same type. In your case - two of them at CHAR, one is INT. Because of that - the dbengine must then uses its own internal rules to determine which type the CASE is returning, and per its own type precedence rules - INT "wins" in this case.
declare @tbl_num int
declare @tbl_num_char char(2)
set @tbl_num = 1
set @tbl_num_char =
case
when @tbl_num = 9 then '10'
when @tbl_num < 9 then '0' + cast((@tbl_num) as char)
when @tbl_num > 9 then cast(@tbl_num as char) --now it's character date, used to be int
end
print @tbl_num
print @tbl_num_char
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 14, 2009 at 3:07 pm
that did the trick!
but why was SQL even looking at that line of code, seeing as how the value of @tbl_num was less than 9?
January 14, 2009 at 3:09 pm
It has to make a decision once and for all what data type each column is going to be. In order to do that - it has to look at ALL possibilities, and therefore all lines of the CASE.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 14, 2009 at 3:10 pm
i see. thank you for clarifying that for me.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply