Arithmetic overflow error converting numeric data to a numeric data type

Question

I keep getting this error message every time I run this request:

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 changed the create table to (7.0), I will not receive an error message. But I need my data to display as decimal. I tried 8.3 does not work.

Is there anyone who can help me with this? Any help would 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 

Answer 1

I assume that you are trying to compress a number greater than 99999.99 into your decimal fields. Changing it to (8.3) is not going to do anything if it is more than 99999.999 - you need to increase the number of digits to a decimal fraction. You can do this by increasing accuracy (this is the total number of digits before and after the decimal number). You can leave the scale the same if you do not need to change the number of decimal places to store. Try decimal(9,2) or decimal(10,2) or something else.

You can verify this by commenting on insert #temp and see what numbers the select statement gives and see if they can handle your column more.

Answer 2

I feel that I need to clarify one very important thing for others (for example, my colleague) who have encountered this thread and received incorrect information.

The answer (“Try decimal (9.2) or decimal (10.2) or something else”) is correct, but the reason (“increase the number of digits to decimal”) is incorrect.

decimal (p, s) and numeric (p, s) and indicate precision and scale . "Accuracy" is not the number of digits to the left of the decimal, but instead the full accuracy of the number.

For example: decimal (2.1) covers from 0.0 to 9.9, since the accuracy is 2 digits (from 00 to 99), and the scale is 1. decimal (4.1) covers from 000.0 to 999.9 decimal (4.2) spans 00.00 - 99.99 decimal (4.3) spans from 0.000 to 9.999

Answer 3

If you want to reduce the size to decimal (7.2) from decimal (9.2), you will have to consider existing data with values ​​large in order to fit in decimal (7.2). Or if you have to remove these numbers, truncate it to fit in the new size. If there was no data for the field you are trying to update, it will do it automatically without any problems.