Answered by:
Arithmetic overflow error converting numeric to data type varchar

I am trying to update a table from another table using a subquery and a sum statement. Both deductible are char fields, it works fine when i dont divide by 100, however; i need the decimal in the right place in order to get the right cents amount. I have tried increasing the left and right sides of the numeric field. Since the detail table contains millions of records its hard to pinpoint what might be causing the overflow. When ever i attempt this i get an error as listed in the title. Any help / advice is appreciated.
Here is the query
UPDATE s SET s.amount = (SELECT SUM(CAST(ISNULL(amount,0) as numeric(18,2)))/100 FROM DETAIL d WHERE d.number = s.number) FROM SUMMARY s
Question
Answers

The problem is the size of the s.amount field. Please post the table definition. You are trying to put a 18 char number into a field which can't handle 18 chars.
 Proposed as answer by scott_morrisga Monday, July 15, 2013 7:18 PM
 Marked as answer by Allen Li  MSFTModerator Tuesday, July 23, 2013 9:00 AM
All replies


It's all about LEN(amount). You need to increase the length of numeric data type. The difference between 18 and 2 is so important.
Please see the behavior in these samples:
 3 digits SELECT LEN(123); >> overflow error SELECT SUM(CAST(ISNULL(123, 0) AS NUMERIC(3, 1))) / 100.00 >> ok! SELECT SUM(CAST(ISNULL(123, 0) AS NUMERIC(3, 0))) / 100.00 >> overflow error SELECT SUM(CAST(ISNULL(123, 0) AS NUMERIC(4, 2))) / 100.00 >> ok! SELECT SUM(CAST(ISNULL(123, 0) AS NUMERIC(5, 2))) / 100.00  4 digits! SELECT LEN(1.23); >> overflow error SELECT SUM(CAST(ISNULL(1.23, 0) AS NUMERIC(3, 3))) / 100.00 >> ok! SELECT SUM(CAST(ISNULL(1.23, 0) AS NUMERIC(3, 2))) / 100.00 >> overflow error SELECT SUM(CAST(ISNULL(1.23, 0) AS NUMERIC(5, 5))) / 100.00 >> ok! SELECT SUM(CAST(ISNULL(1.23, 0) AS NUMERIC(5, 4))) / 100.00
http://sqldevelop.wordpress.com/
 Edited by Saeid Hasani Monday, July 15, 2013 7:27 PM correct




You didn't give us the datatype of amount, but pretty clearly from the error you are getting it is a varchar.The rules for the scale and precision of the result of operations on numeric columns can be complex. You can find some, but not all of those rules at http://msdn.microsoft.com/enus/library/ms190476.aspx.
In particular what is happening to you is you are doing a SUM on an 18,2 value. SQL knows the result will have two digits to the right of the decimal. But it does not know how big the result will be. So it puts the result in the largest numeric type with two decimal places. That is numeric(38,2).
So, for example, suppose the sum is 1234.56. That will be in a numeric(38,2) format, but when you do the implicit conversion to varchar to store store it in amount it becomes the string '1234.56' and takes 7 chararacters. But when you do the divide by 100 and follow the rules given in the above link, you see that divide always gives you at least 6 digits to the right of the decimal point. So the result is a numeric(38,6) number containing 12.345600 and when converted to a string becomes '12.345600'. Note that that is longer than the string before the divide and it now takes 9 characters. Effectively by this divide you are making the string two characters longer. And you geet this error because at least one of your sums is large enough that adding two characters to it causes the string to be too long to fit in amount. You will probably avoid this error if you do
UPDATE s SET s.amount = (SELECT CAST(SUM(CAST(ISNULL(amount,0) as numeric(18,2)))/100 AS numeric(38,4)) FROM DETAIL d WHERE d.number = s.number) FROM SUMMARY s
However, one of the ultimate root cause of your problem is you are storing numeric data in the wrong datatype. If amount is always supposed to have a numeric value, make it the appropriate number data type (int, bigint, numeric(scale, precision), float, etc). An important part of preserving your data integrity is to restrict the datatype of each column to force data to be the correct type.
Tom


The problem is the size of the s.amount field. Please post the table definition. You are trying to put a 18 char number into a field which can't handle 18 chars.
 Proposed as answer by scott_morrisga Monday, July 15, 2013 7:18 PM
 Marked as answer by Allen Li  MSFTModerator Tuesday, July 23, 2013 9:00 AM

Excuse for delay. The difference is so important. You do not need increase both limits.
This may help:
; WITH base AS ( SELECT '1.23' AS amount UNION SELECT '12.345' UNION SELECT '1234.35' ) SELECT MAX(LEN(LEFT(amount, CHARINDEX('.', amount)  1))) AS L  It's so important (the diference) ,MAX(LEN(RIGHT(amount, LEN(amount)  CHARINDEX('.', amount)))) AS R FROM base; ; WITH base AS ( SELECT '1.23' AS amount UNION SELECT '12.345' UNION SELECT '1234.35' ) SELECT CAST(amount AS DECIMAL(4, 0)) AS [DECIMAL(4, 0)] /* 4  0 >= 4 */ ,CAST(amount AS DECIMAL(5, 1)) AS [DECIMAL(5, 1)] /* 5  1 >= 4 */ ,CAST(amount AS DECIMAL(6, 2)) AS [DECIMAL(6, 2)] ,CAST(amount AS DECIMAL(7, 3)) AS [DECIMAL(7, 3)] ,CAST(amount AS DECIMAL(8, 4)) AS [DECIMAL(8, 4)] . . . . ,CAST(amount AS DECIMAL(37, 33)) AS [DECIMAL(37, 33)] ,CAST(amount AS DECIMAL(38, 34)) AS [DECIMAL(38, 34)] FROM base;
http://sqldevelop.wordpress.com/