none
Arithmetic overflow error converting numeric to data type varchar

    Question

  • 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

    Monday, July 15, 2013 3:50 PM

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.

    Monday, July 15, 2013 5:35 PM
    Moderator

All replies

  • Do you get the error without the divide by 100?

    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

    Monday, July 15, 2013 4:27 PM
  • 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
    Monday, July 15, 2013 4:28 PM
  • No i dont get the error without the /100. I did just try *.01 and didnt get the error
    Monday, July 15, 2013 4:38 PM
  • i increased the limit to 30 on left and 10 on the right still same error, no number in my dataset is that big.
    Monday, July 15, 2013 4:39 PM
  • I think that it converts to decimal (18, 4)

    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

    Monday, July 15, 2013 4:39 PM
  • 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/en-us/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

    Monday, July 15, 2013 4:45 PM
  • its in my orignal post:

    Both deductibles are char fields. I should of said both amounts are char fields.

    they come in from our clients as char fields i have no choice but to cast or convert it.



    • Edited by ksb1454 Monday, July 15, 2013 4:50 PM correction
    Monday, July 15, 2013 4:47 PM
  • 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.

    Monday, July 15, 2013 5:35 PM
    Moderator
  • 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/

    Monday, July 15, 2013 7:26 PM