CODEWITHSUNDEEP
sqlsql-serversql-server-2008view
user3129837
user3129837

Reputation: 13

SQL conversion failed when converting

following situation:

a column xy is defined as varchar(25). In a view (SQL Server Mgmt Studio 2008) I filtered all values with letters (-> is not like '%[A-Z]%') and converted it to int (cast(xy as int)).

If I now try to make comprisons with that column (e.g. where xy < 1000), I'm getting a conversion error. And the message contains a value that should have been filtered with "is not like '%[A-Z]%'". Whats wrong??

thanks for help in advance...

this works (it folters out for example value 'G8111'): SELECT unid FROM CD_UNITS AS a INNER JOIN DEF_STATION AS b ON a.STATION = b.STATION WHERE (b.CURENT = 'T') and UNID like '%[A-Z]%'

but when i put that in a view, an make select on it:

select * from my_view where xy < 3000

system says 'Conversion failed when converting the varchar value 'G8111' to data type int.' but 'G8111' should be filtered out in query above...

Upvotes: 0

Views: 876

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239724

The optimizer does crazy things at times, so despite the fact that an "inner" filter1 "should" protect you, the optimizer may still push the conversion lower down than the filter and cause such errors.

The only semi-documented place where it will not do this is within a CASE expression:

The CASE statement(sic) evaluates its conditions sequentially and stops with the first condition whose condition is satisfied. In some situations, an expression is evaluated before a CASE statement receives the results of the expression as its input.

...

You should only depend on order of evaluation of the WHEN conditions for scalar expressions (including non-correlated sub-queries that return scalars), not for aggregate expressions

So the only way that should currently work would be:

CASE WHEN xy NOT LIKE '%[^0-9]%' THEN CONVERT(int,xy) END < 1000

This also uses a double-negative with LIKE to ensure that it only attempts the conversion when the value only contains digits.

1Whether this be in a subquery, a CTE, a View, or even just considering the logical processing order of SELECT and WHERE clauses. Within a single query, the optimizer can and will push conversion operations past filters.

Upvotes: 1

Related Questions