Arithmetic overflow error...Please Help. SQL

Question

I am receiving the error

Arithmetic overflow error converting expression to data type float

on the following code:

SELECT b.[CUSIP NUMBER],
   b.[PASS THRU RATE],
   a.[CURRENT BALANCE],
   b.[ORIGINAL WA MATURITY],
   b.[CURRENT WA MATURITY],
   (b.[ORIGINAL BALANCE] * ((b.[PASS THRU RATE]*.01)/12))/ (1-((1 + power (( (b.[PASS THRU RATE]*.01 )/ 12), -b.[ORIGINAL WA MATURITY] ) )))

   FROM DBO.mbs012013 a, DBO.mbs022013 b
   WHERE a.[CUSIP NUMBER] = b.[CUSIP NUMBER]

I have not designated the numbers to be any specific type so I am not sure why I am receiving this error. If any one can tell me how to fix this it would greatly appreciated.

For reference: cusip number is a serial number, the rest are inputs (interest rate, bank balance, maturity in months, etc.)

And what database system is this for? SQL is just the query language - used by many databases. We need to know if this is for Oracle, Postgres, MySQL, SQL Server, IBM DB2 or something else. Please add the relevant and meaningful tags - thanks! — marc_s
– marc_s, Commented Jun 20, 2013 at 18:26
possible duplicate of Arithmetic overflow error converting numeric to data type numeric and Arithmetic overflow error in SQL — Ken White
– Ken White, Commented Jun 20, 2013 at 18:29
There is no datatype in SQL Server that can cope with 10^360 — Martin Smith
– Martin Smith, Commented Jun 20, 2013 at 18:51

Martin Smith · Accepted Answer · 2013-06-21 17:54:59Z

2

You say PASS THRU RATE can be 1-10 and ORIGINAL WA MATURITY between 0 - 360.

The worst possible case for you then would be PASS THRU RATE = 1 and ORIGINAL WA MATURITY = 360

In that case you would be doing

SELECT POWER(0.0008333333333, -360)

Which has a result of 3.2E+1108. No Datatype in SQL Server has a greater range than float and that "only" allows a range of ± 1.79E+308.

If you need to do these calculations then they will need to be done outside of TSQL. But are you sure that your formula is actually correct?

It shouldn't be one of these?

SELECT POWER(1 + 0.0008333333333, -360)

SELECT POWER(1 - 0.0008333333333, -360)

answered Jun 21, 2013 at 17:54

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

D Stanley · Accepted Answer · 2013-06-20 18:41:54Z

0

The problem is probably here:

power (( (b.[PASS THRU RATE]*.01 )/ 12), -b.[ORIGINAL WA MATURITY] )

If b.[PASS THRU RATE] is small and -b.[ORIGINAL WA MATURITY] is large you can end up with a result that is larger than the NUMERIC type can hold. Try casting them to floats:

power (( CAST(b.[PASS THRU RATE]*.01 / 12) AS FLOAT), -b.[ORIGINAL WA MATURITY] ) )))

answered Jun 20, 2013 at 18:41

D Stanley

153k12 gold badges190 silver badges257 bronze badges

3 Comments

Martin Smith Over a year ago

The error message is complaining about overflow converting to float though.

HABO Over a year ago

@MartinSmith - Curiously, select Power( .01, -350 ) results in Arithmetic overflow error converting expression to data type float..

Martin Smith Over a year ago

@HABO - Well that is 1E+700. Float goes up to 1.79E+308

Collectives™ on Stack Overflow

Arithmetic overflow error...Please Help. SQL

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related