I have a query that was working prior to an upgrade to SQL 2005 from SQL 2000. Now, I am getting an "Error converting data type varchar to numeric." error message. It appears to be related to the order of operations of the "WHERE" clause.
Here is the partial WHERE clause:
AND ISNUMERIC(value) = 1
AND CONVERT(numeric(20,2), value) < 0
AND type IN (...
The value field in the database table is defined as a varchar and will contain mixed datatypes. That is why the "ISNUMERIC" line is in the where clause.
Interestingly, if I change the qualified values in the "IN" line to a single value, it works fine. As soon as I introduce a second value within the "IN" line, I get the error message.
Please see below thread for more details:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=768927&SiteID=1
This is by design. You will have to either use CASE expression to perform conditional checks or modify your schema to use the correct data types for the data.
No comments:
Post a Comment