CODEWITHSUNDEEP
sqlsql-servert-sql
RLH
RLH

Reputation: 15698

Getting SQL 'Arithmetic overflow error converting numeric to data type numeric.' On a statement that is tested for in-bound values

I have the following statement that is executed at the end of a really large stored procedure.

UPDATE myTable
SET    DiffPerc = CAST(CASE
                         WHEN ( CASE
                                  WHEN SimExt = 0
                                       AND StdExt = 0 THEN 0
                                  WHEN StdExt = 0 THEN 99999
                                  ELSE SimExt - StdExt / StdExt
                                END ) * 100 > 99999 THEN 99999
                         ELSE ( CASE
                                  WHEN SimExt = 0
                                       AND StdExt = 0 THEN 0
                                  WHEN StdExt = 0 THEN 99999
                                  ELSE SimExt - StdExt / StdExt
                                END ) * 100
                       END AS DECIMAL(8, 2))

The idea is that I have a fields that determines the percentage difference of one value over another. The field DiffPerc is percentage difference of SimExt to StdExt. This routine has worked every day, for well over a year, however, starting two days ago I started to getting the following error message:

Arithmetic overflow error converting numeric to data type numeric.

I understand what this message means, but the whole point of the embedded case statement is to to both test for 0's in the denominator, as well as check for any grossly high value percentages before the value is packed into the DECIMAL (8, 2) field.

What am I missing? How can I update this statement to account for all possible edge cases and handle any overflow before it happens?

Also, please note that the hard-coded 99999 value as a percent is a a flag to the end users that someone has screwed something up.

Upvotes: 1

Views: 2681

Answers (4)

Johan
Johan

Reputation: 1192

Maybe it is a typo, but it seems to me that you are missing a set of parethesis.

I.e.

(SimExt - StdExt) / StdExt else you will always do SimExt - 1 since the subtraction is done after the dividing.

Note the difference:

select CAST((99999999 - 99999998) / 99999998 as decimal(8,2))
select CAST(99999999 - 99999998 / 99999998 as decimal(8,2))

Upvotes: 1

Dumitrescu Bogdan
Dumitrescu Bogdan

Reputation: 7267

SQL does not do short circuit evaluation, so you have to protect it for that. http://en.wikipedia.org/wiki/Short-circuit_evaluation

Practically it will evaluate both cases and will chose at the end the right path.

Upvotes: 0

podiluska
podiluska

Reputation: 51494

It's probably an out of range high value. Try altering to

  (CASE  
   WHEN SimExt = 0 AND StdExt = 0 THEN 0  
   WHEN StdExt = 0 THEN 99999  
   when simext>1000000 then null
   ELSE SimExt - StdExt / StdExt  
   END) * 100 > 99999

Upvotes: 0

Madhivanan
Madhivanan

Reputation: 13700

The end result exceeds the size (8,2). You need to increase the scale

Upvotes: 0

Related Questions