current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I have to filter a column called [Order] (data type = decimal(3,1)) in my final view for 1.0.

List of unique 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

My SQL Statement is as follows:

SELECT
        [Order]
FROM
        [dbo].[vwCoreData_AllOtherCats]
WHERE
        [MEDate] = '2/29/2008'
    and [Order] = 1
--  and [Order] = 1.0
--  and [Order] = cast(1 as decimal(3,1))
--  and [Order] = cast(1.0 as decimal(3,1))

The commented-out lines shows the different methods I've tried. All 4 ways results in the same error message:

Msg 8115, Level 16, State 8, Line 1

Arithmetic overflow error int to data type numeric

Anyone know what is going on here?

Thanks for any help anyone can provide,

CTB

share|improve this question
1  
which MEDate type is? –  danihp May 21 '13 at 20:08
    
MEDate data type = smalldatetime. MEDate shouldn't be an issue though. If I comment out all [Order] filters and filter strictly on MEDate, query runs fine. It is when I try to filter [Order] = 1 is when it blows up. Thanks for your help. –  Conan Kelly May 21 '13 at 21:36
    
FYI: [Order] is not coming directly from the table as a decimal(3,1), there are several views involved above this one. [Order] is stored as tinyint in the table. In the view directly above this one, [Order] is converted to decimal(3,1) using CAST([Order] AS decimal(3 , 1)). –  Conan Kelly May 21 '13 at 23:21
    
Try casting the other side, not you comparator. CAST([Order] as Numeric(3,1)) = 1. One cannot overflow 1. But a goofy value in Order could. Views, especially views on views get weird... try an exec sp_refreshview - just because a value is moving through them as expect doesn't mean the column definition is still as expected. –  Volvox May 23 '13 at 1:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.