SQL 将数字转换为数据类型数字的算术溢出错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4670903/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Arithmetic overflow error converting numeric to data type numeric
提问by user572984
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.
但是，如果我将创建表更改为 (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'),
('HymanSONVILLE'),
('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', 'HymanSONVILLE',
'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', 'HymanSONVILLE', '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
回答by adam0101
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 beforethe 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 的数字压缩到您的十进制字段中。如果它大于 99999.999，将其更改为 (8,3) 不会做任何事情 - 您需要增加小数点前的位数。您可以通过提高精度（即小数点前后的总位数）来实现这一点。除非您需要更改要存储的小数位数，否则您可以保持比例不变。尝试decimal(9,2)或decimal(10,2)或什么的。
You can test this by commenting out the insert #tempand see what numbers the select statement is giving you and see if they are bigger than your column can handle.
您可以通过注释掉insert #temp并查看 select 语句提供给您的数字来测试这一点，并查看它们是否大于您的列可以处理的大小。
回答by Dan
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.
decimal(p,s) 和 numeric(p,s) 都指定Precision 和 Scale。“精度”不是小数点左边的位数，而是数字的总精度。
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），小数位数是1。decimal(4,1)覆盖000.0到999.9 decimal(4,2)覆盖00.00到99.99 十进制 (4,3) 涵盖 0.000 到 9.999
回答by Benoy John
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) 的现有数据。您要么必须删除这些数字，要么将其截断以适应您的新尺寸。如果您尝试更新的字段没有数据，它将自动执行而不会出现问题
