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
141
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
51
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
-1
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)。你必须删除这些数字,并将其截断以适合你的新尺寸。如果您正在尝试更新的字段没有数据,那么它将自动执行,没有问题。
#1
141
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
51
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
-1
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)。你必须删除这些数字,并将其截断以适合你的新尺寸。如果您正在尝试更新的字段没有数据,那么它将自动执行,没有问题。