Arithmetic overflow error converting numeric to data type numeric

Question

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.

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   

Answer 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.

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.

Answer 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.

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.

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