Arithmetic overflow error converting float to data type numeric

Question

Arithmetic overflow error converting float to data type numeric

apat

SSCertifiable

Points: 5929
More actions
March 5, 2010 at 6:53 am

#219644

Hi All,
I imported some data from text file to sql server 2008. Import Export wizard created a table with varchar(255) for some columns which had float data. Then I altered those columns and converted them to float.
One of these columns (Column1) has data like (-8.92,-10.34,-19.97,-7.62,-17.82,1.2,1.86,0.37) There are 120K records in total. This column has records ranging from -986.145 to 975.81. I have to create a new column from this column by applying this...
select POWER (1.8, Column1) * 10000.0 as NewValue from #temp
When I do this I get following error:
Msg 8115, Level 16, State 6, Line 1
Arithmetic overflow error converting float to data type numeric.
Can someone please help me with this? Thanks in advance...

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

apat (3/5/2010)
Hi All,
I imported some data from text file to sql server 2008. Import Export wizard created a table with varchar(255) for some columns which had float data. Then I altered those columns and converted them to float.
One of these columns (Column1) has data like (-8.92,-10.34,-19.97,-7.62,-17.82,1.2,1.86,0.37) There are 120K records in total. This column has records ranging from -986.145 to 975.81. I have to create a new column from this column by applying this...
select POWER (1.8, Column1) * 10000.0 as NewValue from #temp
When I do this I get following error:
Msg 8115, Level 16, State 6, Line 1
Arithmetic overflow error converting float to data type numeric.
Can someone please help me with this? Thanks in advance...

May be a dumb question, but are you trying to raise 1.8 to the value in column1 or are you trying to raise the value of column1 to the power of 1.8? If the later, you have it coded backwards, it should be POWER(Column1, 1.8) * 10000.0.

LutzM SSC Guru Points: 107049 More actions · Answer 2

Are you really sure you want to calculate 1.8 ^975.81???

Not even sure, what that number will be....

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

lmu92 (3/5/2010)
Are you really sure you want to calculate 1.8 ^975.81???
Not even sure, what that number will be....

1.8 ^ 100 = 33,670,573,242,751,689,858,746,062.7772

apat SSCertifiable Points: 5929 More actions · Answer 4

Lynn,

Thanks for your quick reply.. If I do,

select POWER (column1, 1.8) * 10000.0 as newvalue from #temp with (nolock)

it gives me an error:

Msg 3623, Level 16, State 1, Line 1

An invalid floating point operation occurred.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

apat (3/5/2010)
Lynn,
Thanks for your quick reply.. If I do,
select POWER (column1, 1.8) * 10000.0 as newvalue from #temp with (nolock)
it gives me an error:
Msg 3623, Level 16, State 1, Line 1
An invalid floating point operation occurred.

Not sure what is going on, here is the code I just ran and it works.

create table #temp (

column1 float

);

declare @TestVar float;

set @TestVar = 975.81;

insert into #temp select @TestVar;

select column1, POWER (column1, 1.8) * 10000.0 from #temp;

You may need to verify the data in your table.

apat SSCertifiable Points: 5929 More actions · Answer 6

I think this is because I do have some negative values in my table. and I replaced the values in above code and it doesnt work...

create table #t (

column1 float

);

declare @TestVar float;

set @TestVar = -975.81;

insert into #t select @TestVar;

select column1, POWER (column1, 1.8) * 10000.0 from #t;

------------------------------------------------------------------------------

Msg 3623, Level 16, State 1, Line 10

An invalid floating point operation occurred.

any work around for this?

matzer SSCrazy Eights Points: 9727 More actions · Answer 7

I don't know what you are trying to compute, but there's no workaround for this simply because it's mathematically undefined. You can't raise a negative number to power which is not a whole number. Let's take a more simple example

POWER(x, 1.5)

This is mathematically equivalent to POWER(x, 1) * POWER(x, 0.5) which is equivalent to POWER(x, 1) * SQRT(x). But SQRT(x) is undefined for negative numbers, because for example -2 * -2 = 2 * 2 = 4 which would make SQRT not a function.

Maybe you should try to explain what you are trying to compute.

Peter

BTW: In SQL 2005 you get a domain error, which is a more specific error in this case. No idea why they changed the error message.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Peter Brinkhaus (3/5/2010)
I don't know what you are trying to compute, but there's no workaround for this simply because it's mathematically undefined. You can't raise a negative number to power which is not a whole number. Let's take a more simple example
POWER(x, 1.5)
This is mathematically equivalent to POWER(x, 1) * POWER(x, 0.5) which is equivalent to POWER(x, 1) * SQRT(x). But SQRT(x) is undefined for negative numbers, because for example -2 * -2 = 2 * 2 = 4 which would make SQRT not a function.
Maybe you should try to explain what you are trying to compute.
Peter
BTW: In SQL 2005 you get a domain error, which is a more specific error in this case. No idea why they changed the error message.

Nice explaination, Peter.

matzer SSCrazy Eights Points: 9727 More actions · Answer 9

Thanks Lynn, it's a long time ago I opened a math book but that's what I remembered from it. I'm curious what the OP tries to compute.

Peter

HowardW One Orange Chip Points: 29109 More actions · Answer 10

Yes, not sure what you're trying to accomplish by applying a power to any negative number as even with whole integers the result flip-flops between a positive or negative result depending on whether the power is a positive integer or a negative integer.

Remember that the mathematical notation -x^y is the same as -(x^y), not the same as (-x)^y which you're attempting to perform.

I'm guessing what you might want to do is:

select column1,(POWER(ABS(column1), 1.8)*CASE WHEN column1<0 THEN -1 ELSE 1 end) * 10000.0 from #t

Edit: Fixed missing bracket

apat SSCertifiable Points: 5929 More actions · Answer 11

Thanks Peter & Lynn,

I shall get back to you very soon on exact reqs...

apat SSCertifiable Points: 5929 More actions · Answer 12

can this be true?

create table #t ( column1 float );

declare @TestVar float;

set @TestVar = -975.81;

insert into #t select @TestVar;

select column1, -POWER (abs(column1), 1.8) * CAST(10000 as float) from #t

matzer SSCrazy Eights Points: 9727 More actions · Answer 13

It runs without any error, but what does it mean? Second, CAST(10000 as float) is equivalent to 10000. (with a dot).

Peter

HowardW One Orange Chip Points: 29109 More actions · Answer 14

Yes, but if the value of column 1 is positive, you don't want to make the result negative, hence the case statement in my post