none
SQL Server 2005: Arithmetic overflow error converting int to data type numeric

    Question

  • Hello all,

    I have a view with a column called Order (data type = decimal(3,1))

    Distinct values for Order are:

    • 1.0
    • 2.0
    • 3.0
    • 4.0
    • 5.0
    • 7.0
    • 8.0
    • 9.0
    • 11.0
    • 12.0
    • 13.0

    I need to filter on Order number 1.0

    When I run this code...

    SELECT [Order]
    FROM [dbo].[vwCoreData_AllOtherCats]
    WHERE
        [Order] = 1
    --  [Order] = 1.0
    --  [Order] = CAST(1 as decimal(3,1))
    --  [Order] = CAST(1.0 as decimal(3,1))

    ...I get the error message listed in the title of this post.  (I'm using SSMS views/scripts/queries to run this statement.)

    anybody have an idea what could be going on here?

    (FYI: I've already posted this question on StackOverflow.com.  I'm posting here to hopefully get some more eyeballs on it.  The SO.com posting might have some more relevant info.)

    Thanks for any help anyone can provide,

    CTB

    Wednesday, May 22, 2013 5:43 PM

Answers

    • Order was stored in the table as a tinyint with values of 1 to 13.
    • Implicitly cast to int along the way with the following statement:  (SELECT [Order] FROM dbo.lkpOrder_MPS WHERE (MPS = 'Exclude')) + 1 AS [Order]
    • Explicitly cast to decimal(3,1) along the way with:  CAST([Order] AS decimal(3, 1)) AS [Order]
    • For what ever reason, I kept getting the error trying to filter Order for 1 (or 1.0).
    • Once again, values are only 1 to 13.  No way to have a value > 99.

    I just went to the table and define Order's data type as decimal(4,1) and made sure it trickled down through all the views.  Now I can filter it for Order 1 without errors.

    • Marked as answer by CTBarbarin Wednesday, May 22, 2013 10:25 PM
    Wednesday, May 22, 2013 10:25 PM

All replies

  • FYI:  by the lines that are commented out, you can see the variations that I've run this statement.  All four variations give the same error message.
    Wednesday, May 22, 2013 5:46 PM
  • The information you posted is incomplete, because I cannot reproduce your problem using the script below (tested on SQL Server 2000 and 2008).

    create table vwCoreData_AllOtherCats("order" decimal(3,1))
    go
    
    SELECT [Order]
    FROM [dbo].[vwCoreData_AllOtherCats]
    WHERE
        [Order] = 1
        
    go
    drop table vwCoreData_AllOtherCats
    
    

    Maybe vwCoreData_AllOtherCats is a view that is using some kind of expression to create this "order" column.

    You can run this to find out the data type of the view's column. My guess is that it is not a decimal(3,1).

    select top 0 *
    into test
    from vwCoreData_AllOtherCats
    go
    exec sp_help test
    go
    drop table test
    


    Gert-Jan

    • Proposed as answer by Naomi NModerator Wednesday, May 22, 2013 6:21 PM
    • Unproposed as answer by CTBarbarin Wednesday, May 22, 2013 9:07 PM
    Wednesday, May 22, 2013 6:02 PM
  • The error suggests that you have made a faulty assumption and that the problem lies within the definition of the view.  Post the DDL of the view and the tables involved and we might be able to solve it.  As a test, you can try:

    select cast([Order] as decimal(3,1)) from dbo.vwCoreData_AllOtherCats order by [Order];

    That will likely generate a similar error.  Given the error indicates an int to numeric error (numeric has higher precedence than int), there is some sort of implict conversion occuring within the view itself. 

    • Proposed as answer by Naomi NModerator Wednesday, May 22, 2013 6:21 PM
    • Unproposed as answer by CTBarbarin Wednesday, May 22, 2013 9:07 PM
    Wednesday, May 22, 2013 6:07 PM
  • In the referenced stackoverflow page, you note that the table this view is based on, the column Order is a tinyint and the view converts it to a decimal(3,10.  Which means it may have values which are 100 or greater and those will not fit in a decimal(3,1). 

    You have a where clause saying [Order] = 1, but SQL is allowed to rearrange the query in any manner it desires.  So it can convert the tinyint to a decimal(3,1) and then eliminate all values except 1.0.  If it does this, you will get this error if any value of Order is >= 100.

    The easiest way to fix this would be to change to view so that it returned a decimal(4,1) (or don't  convert it at all, just leave it as a tiny int).  Then any tinyint value would successfully convert.  If that's not possible, then you need to make sure that you never attempt a conversion of an invalid value. 

    If you leave it as a decimal(3,1), at the very least you need to change this query to

    SELECT CASE WHEN [Order] < 100 THEN [Order] ELSE NULL END AS [Order]
    FROM [dbo].[vwCoreData_AllOtherCats]
    WHERE
        [Order] = 1

    You may well need to add a similar CASE statement to the view definition also.

    Tom

    Wednesday, May 22, 2013 6:29 PM
  • Scott Morris,

    Thanks for your help.

    There are too many views/tables to give you the details on everything, but...

    This view is based on another view above it (vwCoreData_Sub).  In that view, Order is an int.  In this view, It is converted to decimal(3,1) by the following statement:

    CAST([Order] AS decimal(3, 1)) AS [Order]

    Order is stored in the table as a tinyint, but it looks like it was implicitly cast to int further up the line (from vwCoreData_Sub) by the following subquery  to calculate an Order number:

    (SELECT [Order] FROM dbo.lkpOrder_MPS WHERE (MPS = 'Exclude')) + 1 AS [Order]

    This is in a view that handles a select few accounts that gets ?merged? (<- is that the right terminology for UNION ALL views?) back into the remaining records where Order is unaltered (a tinyint coming from the table).

    Thanks again,

    CTB


    • Edited by CTBarbarin Wednesday, May 22, 2013 6:45 PM
    Wednesday, May 22, 2013 6:42 PM
  • Here is an example that shows what Tom has explained.

    SET NOCOUNT ON;
    USE tempdb;
    GO
    CREATE VIEW dbo.vw_MyView
    AS
    SELECT
    	c1, CAST(c1 AS decimal(3, 1)) AS c2
    FROM
    	(VALUES (1), (99), (100)) AS T(c1)
    WHERE c1 < 100;
    GO
    SELECT
    	c1, c2
    FROM 
    	dbo.vw_MyView;
    GO
    SELECT
    	c1, c2
    FROM
    	dbo.vw_MyView
    WHERE
    	c2 < 99.0;
    GO
    DROP VIEW dbo.vw_MyView;
    GO

    For the last query, SQL Server has combined the filter "c2 < 99.0" with the one used in the view declaration and this will throw an error.

    Msg 8115, Level 16, State 8, Line 1
    Arithmetic overflow error converting int to data type numeric.

     


    AMB

    Some guidelines for posting questions...

    Wednesday, May 22, 2013 7:03 PM
    Moderator
    • Order was stored in the table as a tinyint with values of 1 to 13.
    • Implicitly cast to int along the way with the following statement:  (SELECT [Order] FROM dbo.lkpOrder_MPS WHERE (MPS = 'Exclude')) + 1 AS [Order]
    • Explicitly cast to decimal(3,1) along the way with:  CAST([Order] AS decimal(3, 1)) AS [Order]
    • For what ever reason, I kept getting the error trying to filter Order for 1 (or 1.0).
    • Once again, values are only 1 to 13.  No way to have a value > 99.

    I just went to the table and define Order's data type as decimal(4,1) and made sure it trickled down through all the views.  Now I can filter it for Order 1 without errors.

    • Marked as answer by CTBarbarin Wednesday, May 22, 2013 10:25 PM
    Wednesday, May 22, 2013 10:25 PM