0

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

Community
  • 1
  • 1
InigoMontoya
  • 49
  • 5
  • 1
    which MEDate type is? – dani herrera 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. – InigoMontoya 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))**. – InigoMontoya 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 01:46

0 Answers0