SQL 8115：将数值转换为数据类型数值时出现算术溢出错误

Question

我在我的 SQL Server 2005 上遇到了一个错误

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

当我尝试在类型为DECIMAL(10,3)的列MYCOLUMN中插入一个值=X(DECIMAL(3,2)) * Y(INT)时，会出现问题。

如何解决这个问题呢？

以下是一些代码，以便更好地理解情况：

-- ============================================================
--   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 

- serhio

---

使用正确的数据类型？看起来DECIMAL(10,3)不够大。除非您告诉我们X和Y的最大值，否则我们无法告诉您哪种数据类型是正确的。 - Aaron Bertrand

@serhio 我认为他的意思就是他说的话。您预计传递给 X 和 Y 的最小值和最大值是多少？ - Mansfield

好的，但是如果您有一个像7777777.777这样的值，并将其乘以3，您明白为什么该值不再适合DECIMAL（10,3）了吗？ - Aaron Bertrand

我还觉得你在精度和范围方面存在概念问题。它们不是“小数点前的数字”和“小数点后的数字”——你尝试过在 DECIMAL（10,3） 中存储9999999999.999 吗？ - Aaron Bertrand

好的，伙计们，假设最大值为 1 111 111 111,111。 - serhio

Answer 1

必要的比例、精度和长度规则在MSDN文档中有很好的涵盖。

十进制数只是一个缩放的整数，因此让我们看一个没有小数点的例子。

例如，X DECIMAL(3,0) 和 Y DECIMAL(3,0)，其中 X=100 且 Y=100，X*Y=10000，这将不适合于 DECIMAL(3,0)，而需要 DECIMAL(5,0)。实际上，999*999=998001，这将需要 DECIMAL(6,0)。

因此，尝试插入两个十进制列的乘积结果只能匹配如果目标列具有与MSDN表格中不同操作指示的公式至少相同的容量。

- Cade Roux

Answer 2

你有两个选择：

一个是扩大表格#GP_WGENSVCTH中VALUE字段的大小。显然decimal(10,3)无法容纳您尝试插入的值。

另一个是将#GP_WGENSVCTH表中的列数据类型更改为float。

VALUE float

将数据类型改为浮点型后，您就不必担心值超出您所设置的小数精度和范围了。

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

declare @float float

set @float = @dec * @int

select @float

请查看上述代码的演示 SQL Fiddle。

- Taryn

Answer 3

假设你在评论中提到的X和Y的最大乘积可以达到1 111 111 111,111，只需将MyColumn更改为decimal(13, 3)即可。

试一试：

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

没有错误。

你不能将1 111 111 111,111存储在decimal(10, 3)中。

- LittleBobbyTables - Au Revoir

---

很抱歉，我在SQL方面还很新，所以请原谅。我发布了一些代码来解释。请考虑最大的1,111,111,111。 - serhio