Woes with converts in case statements

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Woes with converts in case statements

Author

Topic

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-12-13 : 19:59:05

I have a table with columns a,b,c
column a is simply an int identity
column b is an identifier
and column c is my value

create table t1(
a int identity(1,1),
b int,
c varchar(10))

Now this table is populated by a text file with the following entrees
a,b,c
1,1,394.0000
2,2,0.2930
3,1,230.0000
4,3,1.3400

The values in c are unformatted... those extra 0's at the end are added and not needed. Often leading 0's are added as well. The thing is, when b = 1 the entry shoould not have nulls (format of numeric(4,0). if it's a 2 it needs to be numeric(4,3) and if it's a 3 it should be numeric(3,2)

I ran this statement
update t1
set c = case
when b = 1 then convert(numeric(4,0),c)
when b = 2 then convert(numeric(3,2),c)
when b = 3 then convert(numeric(4,3),c)
end

Strangely enough all values coming out were to 3 decimal places. After a bit I discovered why... SQL when it parses this statement goes through the case statement and determines the largest possible outcome of the case and sets the results to that. Had that case statement above contained the line
when b = 1 then convert(numeric(6,5),c)
all lines would have came out to 5 decimal places. Any idea how to go about formatting this? My main concerns is the table has 1million rows and theres are 18 different 'b's in there.... any input appreciated.

-----------------------
SQL isn't just a hobby, It's an addiction

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-13 : 22:30:33

Would this work for you

update #t1
set c = case
when b = 1 then rtrim(ltrim(str(c,4,0)))
when b = 2 then rtrim(ltrim(str(c,4,2)))
when b = 3 then rtrim(ltrim(str(c,5,3)))
end

The difference is that when b=2 the convert will give .29 while
this method will give 0.29.

Edited by - ValterBorges on 12/13/2002 22:36:10

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-12-17 : 11:40:57

Unfortunately no... Some times the last 0 is required (precision or whatever the term for it is)

Perhaps use a trim then add 0's from there... I think rob gave me a nice script for that in the past

-----------------------
SQL isn't just a hobby, It's an addiction

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-12-17 : 12:07:33

Don't you just need to ensure that the values are converted back to varchar before they escape from the CASE expression?


update t1
set c = case
when b = 1 then convert(varchar(10),convert(numeric(4,0),c))
when b = 2 then convert(varchar(10),convert(numeric(3,2),c))
when b = 3 then convert(varchar(10),convert(numeric(4,3),c))
end

etc.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-17 : 12:29:21

Isn't this a formatting issue, not a storage issue? You can't store multiple datatypes in 1 field (though I guess with sqlvariant you can but I haven't used that yet).

Just store what you get, with as much precision as you need. When SELECTING the data, do your formatting.

- Jeff

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-12-17 : 13:01:40

Yes, I thought that too jeff... And pointed it out. Unfortunately our DB designer thinks otherwise. Bleh, who am I to argue?

Fribble... That looks like a great idea, I'll give it a try

-----------------------
SQL isn't just a hobby, It's an addiction

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources