1

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.)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user2501239
  • 149
  • 1
  • 8
  • 2
    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 Jun 20 '13 at 18:26
  • Sorry about that, its SQL Server – user2501239 Jun 20 '13 at 18:28
  • possible duplicate of [Arithmetic overflow error converting numeric to data type numeric](http://stackoverflow.com/questions/4670903/arithmetic-overflow-error-converting-numeric-to-data-type-numeric) and [Arithmetic overflow error in SQL](http://stackoverflow.com/q/8524223/62576) – Ken White Jun 20 '13 at 18:29
  • 2
    You probably divide by zero or something like this – juergen d Jun 20 '13 at 18:30
  • 2012, and no this isnt a duplicate – user2501239 Jun 20 '13 at 18:33
  • This will be coming from the `POWER` function. e.g. `SELECT POWER(10,309)` gives this error. What are the values of the parameters? Float can go up to `1.79E+308`. – Martin Smith Jun 20 '13 at 18:48
  • Pass through rate ranges from 1-10 and the original WA Maturity ranges from 0 - 360 – user2501239 Jun 20 '13 at 18:50
  • How about providing some sample data so we can actually figure this out without wild guessing? – ErikE Jun 20 '13 at 18:50
  • 2
    There is no datatype in SQL Server that can cope with `10^360` – Martin Smith Jun 20 '13 at 18:51
  • Okay thank you all for the help – user2501239 Jun 20 '13 at 18:53
  • Start with `SELECT 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]` and take it apart a piece at a time until you find the problem. If you are raising a number to a large _negative_ power you may be dropping below epsilon and dividing by `1 - 1`. – HABO Jun 20 '13 at 18:54

2 Answers2

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)
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
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] ) )))
D Stanley
  • 149,601
  • 11
  • 178
  • 240