Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL 8115 Arithmetic overflow error converting numeric to data type numeric

I obtain on my SQL Server 2005 an error

Server: Msg 8115, 
Arithmetic overflow error converting numeric to data type numeric.

When I try to insert a value = X(DECIMAL(3,2)) * Y(INT)
in a column MYCOLUMN typed DECIMAL (10,3)

How to workaround this problem?

UPDATE

some code bellow to understand the situation

-- ============================================================
--   Table : GP_WGENTH
-- ============================================================
CREATE TABLE #GP_WGENTH
(
     KEYINTERNAL                     CHAR(32)              NOT NULL,
     KEYHOUR                         INT                   NOT NULL,
     FIRM                            CHAR(5)               NOT NULL,
     CENTRE                          CHAR(2)               NOT NULL,
     SERVICE                         CHAR(6)               NOT NULL,
     AGENT                           CHAR(5)               NOT NULL,
     VERSION                         VARCHAR(3)            NOT NULL,
     DATE_ROULEMENT                  DATETIME              NOT NULL,
     SERVICEINSTANCE                 SMALLINT              NOT NULL,
     TYPEHOUR                        CHAR(9)               NOT NULL,
     QUANTITEFIXE                    DECIMAL(12,2)         NULL    ,
     TYPECMP                         INT                   NULL    ,
     PERCENTCALC                     DECIMAL(3,2)          NULL    
)
-- ============================================================
--   Table : GP_WGENSVCTH
-- ============================================================
CREATE TABLE #GP_WGENSVCTH
(
    KEYINTERNAL                     CHAR(32)              NOT NULL,
    KEYHOUR                         INT                   NOT NULL,
    FIRM                            CHAR(5)               NOT NULL,
    CENTRE                          CHAR(2)               NOT NULL,
    SERVICE                         CHAR(6)               NOT NULL,
    AGENT                           CHAR(5)               NOT NULL,
    VERSION                         VARCHAR(3)            NOT NULL,
    DATE_ROULEMENT                  DATETIME              NOT NULL,
    SERVICEINSTANCE                 SMALLINT              NOT NULL,
    TYPEHOUR                        CHAR(9)               NOT NULL,
    VALUE                           DECIMAL(10,3)         NOT NULL
)
GO

INSERT INTO #GP_WGENSVCTH 
            (KEYINTERNAL, 
             KEYHOUR, 
             FIRM, 
             CENTRE, 
             SERVICE, 
             AGENT, 
             VERSION, 
             DATE_ROULEMENT, 
             SERVICEINSTANCE, 
             TYPEHOUR, 
             VALUE) 
SELECT WTH.KEYINTERNAL, 
       WTH.KEYHOUR, 
       WTH.FIRM, 
       WTH.CENTRE, 
       WTH.SERVICE, 
       WTH.AGENT, 
       WTH.VERSION, 
       WTH.DATE_ROULEMENT, 
       WTH.SERVICEINSTANCE, 
       WTH.TYPEHOUR, 
       WTH.PERCENTCALC * SUM(SC.LENGTH) -- !!! BOOOM !!!!!!!!! HERE
FROM   #GP_WGENTH WTH 
       INNER JOIN GP_SERVICE_HABILLAGE_COMPONENT SC 
               ON WTH.FIRM = SC.FIRM 
                  AND WTH.CENTRE = SC.CENTRE 
                  AND WTH.SERVICE = SC.SERVICE 
                  AND WTH.VERSION = SC.VERSION 
                  AND WTH.TYPECMP = SC.TYPECMP 
WHERE  WTH.KEYINTERNAL = 'august 23 2012  10:45:21:027PM     ' 
       AND SC.LENGTH IS NOT NULL 
GROUP  BY WTH.KEYINTERNAL, 
          WTH.KEYHOUR, 
          WTH.FIRM, 
          WTH.CENTRE, 
          WTH.SERVICE, 
          WTH.AGENT, 
          WTH.VERSION, 
          WTH.DATE_ROULEMENT, 
          WTH.SERVICEINSTANCE, 
          WTH.TYPEHOUR, 
          WTH.PERCENTCALC 
like image 313
serhio Avatar asked Aug 23 '12 18:08

serhio


People also ask

How do you solve arithmetic overflow error converting numeric to data type numeric?

You need to increase the width of the variable to store this number e.g. making @sample NUMERIC (6,2) will solve this error.

How can we avoid arithmetic overflow in SQL?

The solution to avoid this arithmetic overflow error is to change the data type from INT to BIGINT or DECIMAL(11,0) for example.

What does arithmetic overflow error convert to data type int mean?

The error "Arithmetic overflow error converting IDENTITY to data type int" comes when the IDENTITY value is inserted into a column of data type int, but the value is out-of-range.

What is overflow error in SQL?

In general, a data type overflow error is when the data type used to store data was not large enough to hold the data. Furthermore, some data types can only store numbers up to a certain size. An overflow error will be produced, for example, if a data type is a single byte and the data to be stored is greater than 256.


2 Answers

The rules for the necessary scale, precision and length are well covered in MSDN documentation.

Decimal is just a scaled integer, so let's look at an example with no decimal point.

For example X DECIMAL(3, 0) and Y DECIMAL(3, 0) where X = 100 and Y = 100, X * Y = 10000 which will not fit in DECIMAL(3, 0), and requires DECIMAL(5, 0). In fact, 999 * 999 = 998001 which would require DECIMAL(6, 0).

So trying to insert the result of multiplying two decimal columns will only fit if the destination column has a capacity at least as great as that indicated by the formulas in the MSDN table for the different operations.

like image 196
Cade Roux Avatar answered Oct 06 '22 01:10

Cade Roux


You have two options here:

One is to expand the size of the VALUE field in the table #GP_WGENSVCTH. Obviously the decimal(10,3) is not large enough to hold the value you are trying to insert.

Two would be to change the datatype of the column in the table #GP_WGENSVCTH to a float

VALUE float

Changing to a float, you will not have to worry about the value being too large for the precision and scale you set for the decimal

declare @dec decimal(10, 3) = 1111111.111 
declare @int int = 3

declare @float float

set @float = @dec * @int

select @float

See SQL Fiddle with demo of the above code

like image 38
Taryn Avatar answered Oct 06 '22 00:10

Taryn