September 9, 2009 at 8:27 am
create table test_check
(
[orderID] int not null,
[rate] float not null
)
insert into test_check values(1,0.024) --return 2.4
insert into test_check values(2,0.00024)--return 2.4
insert into test_check values(3,0.00000024) --return 2.4
insert into test_check values(4,0.00000024)--return 2.4
insert into test_check values(5,0.0024) --return 2.4
insert into test_check values(6,2.443E-14) --return 2.443
insert into test_check values(7,5.8) --return 5.8
insert into test_check values(8,10.44) --return 10.44
I have data stored in my table in the foll:manner.There was a bug in the front end app, and so data has got inserted like this.
My requirement now is to update all these values in the rate field to the new value mentioned.
I think the flow is like if
after the decimal point there is 0 - divide by 100
000 - divide by 10000
00000 - divide by 1000000
But after the decimal point if there is 00 - divide by 10000
0000 - divide by 1000000
Don't really know how to go about it..also should I be using a cursor to update each record?
Would be grateful if somebody could shed some light
September 9, 2009 at 1:47 pm
are you looking for something like this:
select orderid, rate,
case
When rate > 1 then rate
When rate > 0.1 then rate * 10
When rate > 0.01 then rate * 100
When rate > 0.001 then rate * 1000
When rate > 0.0001 then rate * 10000
When rate > 0.00001 then rate * 100000
When rate > 0.000001 then rate * 1000000
When rate > 0.0000001 then rate * 10000000
When rate > 0.00000001 then rate * 100000000
When rate > 0.0000000001 then rate * 100000000
When rate > 0.00000000001 then rate * 1000000000
When rate > 0.000000000001 then rate * 10000000000
When rate > 0.0000000000001 then rate * 100000000000
When rate > 0.00000000000001 then rate * 100000000000000
end
from test_check
this sounds like homework
September 9, 2009 at 10:47 pm
Thank you so much!!
But I was looking for some way in which I needn't have to write so many case stmts For eg:if the value in the table was 2.443E -21
It becomes difficult, when the values in the table can go upto that limit..
CAn we write using loop stmts or something..I remember writing in C things like this back in school,but not sure abt it in sql
September 9, 2009 at 10:51 pm
Thank you so much!!
But I was looking for some way in which I needn't have to write so many case stmts For eg:if the value in the table was 2.443E -21
It becomes difficult, when the values in the table can go upto that limit..
CAn we write using loop stmts or something..I remember writing in C things like this back in school,but not sure abt it in sql
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply