算术溢出错误将数值转换为数据类型数值。

I keep getting this error message everytime I run this query:

每次运行这个查询时，我都会得到这个错误信息:

Msg 8115, Level 16, State 8, Line 33
Arithmetic overflow error converting numeric to data type numeric.
The statement has been terminated.

But if I change the create table to (7,0), I don't get the error message.But I need my data to be displayed as a decimal. I have tried 8,3 does not work.

但是如果我将create table更改为(7,0)，则不会得到错误消息。但我需要将数据显示为小数。我试过了，8,3不行。

Is there any one who can help me work this?Any help will be greatly appreciated.

有没有人能帮我做这件事?如有任何帮助，我们将不胜感激。

DECLARE @StartDate AS DATETIME
DECLARE @StartDate_y AS DATETIME
DECLARE @EndDate AS DATETIME
DECLARE @temp_y AS DATETIME

SET @temp_y = Dateadd(yy, Datediff(yy, 0, Getdate()), 0)
SET @StartDate_y = Dateadd(dd, 1 - Datepart(dw, Dateadd("ww", -2, @temp_y)),
                                      Dateadd("ww", -2, @temp_y))
SET @StartDate = Dateadd(dd, 1 - Datepart(dw, Dateadd("ww", -2, Getdate())),
                                  Dateadd("ww", -2, Getdate()))
SET @EndDate = Dateadd(dd, 6, @StartDate)

--temp table to hold all cities in list
CREATE TABLE ##temp
  (
     city VARCHAR(50)
  )

INSERT INTO ##temp
VALUES     ('ABERDEEN'),
            ('CHESAPEAKE'),
            ('Preffered-Seafood/CHICAGO'),
            ('Preffered-Redist/CHICAGO'),
            ('CLACKAMAS'),
            ('COLUMBUS'),
            ('CONKLIN'),
            ('DENVER'),
            ('FORT WORTH'),
            ('HANOVER PARK'),
            ('JACKSONVILLE'),
            ('LAKELAND'),
            ('MONTGOMERY'),
            ('PFW-NORTHEAST'),
            ('PFW-SOUTHEAST'),
            ('RIVERSIDE'),
            ('TRENTON,CANADA'),
            ('VERNON')

--temp to hold data for the cities
CREATE TABLE #temp
  (
     city            VARCHAR(50),
     ytdshipments    INT,
     ytdtotalweight  DECIMAL(7, 2) NOT NULL,
     ytdtotalcharges DECIMAL (7, 2) NOT NULL
  --YTDRevperPound decimal (7,2) not null
  )

INSERT INTO #temp
SELECT ##temp.city,
       0,
       0,
       0
FROM   ##temp

INSERT #temp
-- YTD shipments/Charges/Weight by city
SELECT city = CASE
                WHEN nameaddrmstr_1.city IN( 'ABERDEEN', 'CHESAPEAKE', 'CHICAGO'
                                             ,
                                             'CLACKAMAS',
                                             'COLUMBUS', 'CONKLIN', 'DENVER',
                                             'FORT WORTH',
                                             'HANOVER PARK', 'JACKSONVILLE',
                                             'LAKELAND'
                                             ,
                                             'MONTGOMERY'
                                                    ,
                                             'RIVERSIDE', 'TRENTON', 'VERNON' )
              THEN
                CASE
                  WHEN
              nameaddrmstr_1.city = 'CHICAGO'
              AND h.shipr = 'PREFRESVS' THEN 'Preffered-Redist/CHICAGO'
                WHEN
              nameaddrmstr_1.city = 'TRENTON'
              AND nameaddrmstr_1.city = 'CA' THEN 'TRENTON,CANADA'
                ELSE
              nameaddrmstr_1.city
                END
                ELSE 'Other'
              END,
       ytdshipments = COUNT(CONVERT(VARCHAR(10), h.dateshipped, 101)),
       ytdtotalweight =SUM(CASE
                             WHEN h.totaldimwgt > h.totalwgt THEN h.totaldimwgt
                             ELSE h.totalwgt
                           END),
       ytdtotalcharges = SUM (cs.totalestrevcharges)
--YTDRevperPound = convert(decimal(7,2),sum (cs.TotalEstRevCharges )/sum( CASE WHEN h.TotalDimWGT > > h.TotalWGT THEN h.TotalDimWGT ELSE h.TotalWGT END ))
FROM   as400.dbo.hawb AS h WITH(nolock)
       INNER JOIN as400.dbo.chargesummary AS cs
         ON h.hawbnum = cs.hawbnum
       LEFT OUTER JOIN as400.dbo.nameaddrmstr AS nameaddrmstr_1
         ON h.shipr = nameaddrmstr_1.nameaddrcode
WHERE  h.dateshipped >= '01/01/2010'
       AND h.dateshipped <= '12/19/2010'
       --WHERE H.DateShipped >= >= @StartDate_y AND H.dateshipped <= @EndDate 
       AND h.cust IN( 'DARDENREED', 'MAINEDARDE', 'MBMRIVRSDE', 'MBMCOLUMBS',
                      'MBMLAKELND', 'MBMFTWORTH', 'SYGMACOLUM', 'SYGMANETW6',
                      'MAI215', 'MBMMNTGMRY' )
GROUP  BY CASE
  WHEN nameaddrmstr_1.city IN( 'ABERDEEN', 'CHESAPEAKE', 'CHICAGO', 'CLACKAMAS',
                               'COLUMBUS', 'CONKLIN', 'DENVER', 'FORT WORTH',
                               'HANOVER PARK', 'JACKSONVILLE', 'LAKELAND',
                               'MONTGOMERY'
                                      ,
                               'RIVERSIDE', 'TRENTON', 'VERNON' ) THEN CASE
                                                                         WHEN
nameaddrmstr_1.city = 'CHICAGO'
AND h.shipr = 'PREFRESVS' THEN 'Preffered-Redist/CHICAGO'
                                                                         WHEN
nameaddrmstr_1.city = 'TRENTON'
AND nameaddrmstr_1.city = 'CA' THEN 'TRENTON,CANADA'
                                                                         ELSE
nameaddrmstr_1.city
                                                                       END
  ELSE 'Other'
END

SELECT #temp.city                 AS city,
       MAX(#temp.ytdshipments)    AS ytdshipments,
       MAX(#temp.ytdtotalweight)  AS ytdtotalweight,
       MAX(#temp.ytdtotalcharges) AS ytdtotalcharges
FROM   #temp WITH(nolock)
       LEFT OUTER JOIN ##temp
         ON ##temp.city = #temp.city
GROUP  BY #temp.city

DROP TABLE #temp

DROP TABLE ##temp  

3 个解决方案

#1

My guess is that you're trying to squeeze a number greater than 99999.99 into your decimal fields. Changing it to (8,3) isn't going to do anything if it's greater than 99999.999 - you need to increase the number of digits before the decimal. You can do this by increasing the precision (which is the total number of digits before and after the decimal). You can leave the scale the same unless you need to alter how many decimal places to store. Try decimal(9,2) or decimal(10,2) or whatever.

我的猜测是，您试图将一个大于99999.99的数字压缩到您的十进制字段中。将它改为(8,3)如果它大于99999.999，就不会做任何事情——你需要在小数点前增加位数。您可以通过增加精度来实现(这是十进制之前和之后的数字总数)。除非您需要更改要存储的小数位数，否则您可以将scale保持不变。尝试小数(9,2)或小数(10,2)或其他。

You can test this by commenting out the insert #temp and see what numbers the select statement is giving you and see if they are bigger than your column can handle.

您可以通过注释插入#temp来测试这个，看看select语句给出的数字是多少，看看它们是否比您的列能够处理的大。

#2

I feel I need to clarify one very important thing, for others (like my co-worker) who came across this thread and got the wrong information.

我觉得我需要澄清一件非常重要的事情，对其他人(比如我的同事)来说，他们遇到了这个问题，得到了错误的信息。

The answer given ("Try decimal(9,2) or decimal(10,2) or whatever.") is correct, but the reason ("increase the number of digits before the decimal") is wrong.

给出的答案是(“尝试小数(9,2)或十进制(10,2)或其他”)是正确的，但原因(“增加小数点前位数”)是错误的。

decimal(p,s) and numeric(p,s) both specify a Precision and a Scale. The "precision" is not the number of digits to the left of the decimal, but instead is the total precision of the number.

小数(p,s)和数值(p,s)都指定了精度和比例。“精度”不是小数点左边的位数，而是数字的总精度。

For example: decimal(2,1) covers 0.0 to 9.9, because the precision is 2 digits (00 to 99) and the scale is 1. decimal(4,1) covers 000.0 to 999.9 decimal(4,2) covers 00.00 to 99.99 decimal(4,3) covers 0.000 to 9.999

例如:decimal(2,1)覆盖0.0到9.9，因为精度为2位数(00 - 99)，而scale为1。十进制(4,1)涵盖了000.0到999.9的小数(4,2)，包括0.00至99.99的小数(4,3)，包括0.000到9.999。

#3

If you want to reduce the size to decimal(7,2) from decimal(9,2) you will have to account for the existing data with values greater to fit into decimal(7,2). Either you will have to delete those numbers are truncate it down to fit into your new size. If there was no data for the field you are trying to update it will do it automatically without issues

如果您想将大小从小数(9,2)缩小到十进制(7,2)，那么您将必须考虑到现有的数据，其值更大，以适合十进制(7,2)。你必须删除这些数字，并将其截断以适合你的新尺寸。如果您正在尝试更新的字段没有数据，那么它将自动执行，没有问题。