SQL 8115 Arithmetic overflow error converting numeric to data type numeric

Question

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 

Shrum

Use the right data type? Seems like DECIMAL(10,3) isn't large enough. We can't tell you what's right unless you tell us the maximum values for X and Y. – Bratislava

@Aaron Bertrand: what do you mean "maximum values for X and Y"? – Shrum

@Shrum I assume he means exactly what he says. What are the smallest and biggest values that you expect to pass to X and Y? – Grindle

I need to convert or round the result of the multiplication to Decimal (10,3) – Shrum

@Mansfield, the biggest value for an Decimal(10,3) should be "9 999 999 999,999" – Shrum

Ok, but if you have a value like 7777777.777 and you multiply by 3, do you understand why that value no longer fits in a DECIMAL(10,3)? – Bratislava

I also think you are having a conceptual problem with precision and scale. They are not "digits before the decimal" and "digits after the decimal" - did you try storing 9999999999.999 in a DECIMAL(10,3)? – Bratislava

ok, guys, let's say the maximum value is 1 111 111 111,111 – Shrum

Still not possible. Try to insert 1111111111.111 into a DECIMAL(10,3). And why do you think the max value of Y isn't important? We need to know what the max value of the product will be in order to tell you which data type and with what precision / scale you should be using. Multiplying a large value by 10 vs. 50000 vs. 1 billion (all valid values for Y since it's an INT), the capacity you need is quite different. If I told you to go buy some beer for my company picnic, wouldn't you be interested in how many employees I have, and how many beers per person? – Bratislava

I posted some code to explain the situation – Shrum

sorry I am new in SQL, so please forgive me. I posted some code to explain. consider the maximum 1 111 111,111 – Shrum

Answer 1

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.

Lashaun

Answer 2

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

Langrage

Answer 3

Assuming the largest product of X and Y can be 1 111 111 111,111 as you indicated in your comments, just change MyColumn to decimal(13, 3).

Give it a try:

DECLARE @x decimal(13, 3)
SET @x = 1111111111.111 

No error.

You simply can't store 1 111 111 111,111 in decimal(10, 3).

Barna

sorry I am new in SQL, so please forgive me. I posted some code to explain. consider the maximum 1 111 111,111 – Shrum

Answer 4

It's simply a formatting error on your WHERE line. That last colon is not a colon, it should be a decimal:

WHERE  WTH.KEYINTERNAL = '2012 08 23 22:45:21.027'  

Aile