Please help with: Arithmetic overflow error

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

Transact-SQL (2000)

Please help with: Arithmetic overflow error

Author

Topic

swanagetown
Starting Member

19 Posts

Posted - 2005-03-29 : 07:56:40

[Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error converting expression to data type numeric.

Hi,

I'm new to sql and I don't understand this, If I use:

[Code] SELECT 370 As Col1, 1-1/POWER(1.0037,240) As Col2 FROM Table1 [/Code]

This works fine and the results are 370 for Col1 and 0.587849812471664674607426 for Col2

However if I use:

[Code] SELECT 370/(1-1/POWER(1.0037,240)) As Col2 FROM Table1 [/Code]

Then I get the error message above.
Col2 is a Decimal. precision 38, scale 33

The weird thing is the queries below work:

370/(1-1*POWER(1.0037,240)) (changed / to *)
370/(1-1-POWER(1.0037,240)) (changed / to -)
370/(1-1+POWER(1.0037,240)) (changed / to +)
370 / 0.587849812471664674607426 (returns 629.41246582065)

Is there a way to get this to work with /? If it's a precision/scale issue, is there a workaround? if not, can it be done with something like this?

[Code] SELECT 370 As Col1, 1-1/POWER(1.0037,240) As Col2, Results Col1 / Results Col2 As Col3 FROM Table1 [/Code]

Many thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-03-29 : 08:13:50

SELECT 370.0/convert(numeric(12,2),(1-1/POWER(1.0037,240))) As Col2 from Table

Madhivanan

Failing to plan is Planning to fail

swanagetown
Starting Member

19 Posts

Posted - 2005-03-29 : 09:51:04

Many thanks Madhivanan

That did the trick nicely

swanagetown
Starting Member

19 Posts

Posted - 2005-03-29 : 10:21:31

That's really good just what I needed. I have another quick question if I may please.

I just wondered is it possible to use the result generated by that code as the basis of a calucaltion for another column? i.e. something like:

SELECT 370.0/convert(numeric(12,2),(1-1/POWER(1.0037,240))) As Col1, Col1 * 20 As Col2 from Table

At the moment this obviously doesn't work as "Col1" in "Col1 * 20 As Col2" is not calculated. Is there a way to make Col1 calculate before Col1 * 20 As Col2 is executed.(I mean without saving Col1 back to db)

Thanks again

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-29 : 10:54:31

you need to use a derived table:


select col1 + col2 as col3
from 
  (select <something> as col1, <something else> as col2 from ... )  tmp

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources