Conversion Failed in Views - by CarlosN

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


2
0
Sign in
to vote
ID 174527 Comments
Status Closed Workarounds
Type Bug Repros 1
Opened 8/1/2006 8:12:17 AM
Access Restriction Public

Description

There is a table called TABLEA that has a varchar(255) field called FIELD1 which contains numeric and alphanumeric values. I have created a view called TEST_VIEW that references the TABLE_A, converts the FIELD1 to an int and the where clause makes sure only integer values are returned.

e.g:

SELECT  CAST(Field1 AS int) AS Field1, Field2, Field3
FROM   TABLEA
WHERE  isnumeric(Field1) = 1

The TEST_VIEW works when I do a select *, but if I introduce a WHERE clause using the Field1. I get an error message.

Additional Info: This error is only reproduced when an Index is defined with the Field1, i.e. when I remove the index the query works.
Sign in to post a comment.
Posted by Microsoft on 10/9/2006 at 11:30 AM
Hi,

Thanks for your feedback. Here is the response from the developer - please let us know if you need any further assistance.

"This failure is expected, there is no guarantee which order will be taken between project and select operators during the execution plan, it's up to QO. Looking at a the execution plan here, we see that the projection is pushed all the way to the table scan, and the convert along with it. The select filter isnumeric() is executed after which explains why the query fails: not all rows can be casted to integers! In this case the customer needs to rewrite the query. Some solutions below:

create view v2
as
    select field1, field1asint=
        case
            when isnumeric(field1) = 1 then cast(field1 as int)
            else null
        end
    from tabA;
this forces the isnumeric to be executed first and the casting second. Then run this query to obtain the results:
select * from v2 where field1asint is not null;

or if there is an index on field1 then we might as well use it by doing string comparisons and therefore converting the 10 to varchar as in:

select field1
from tabA
where convert(varchar(255),'10') = field1;

but this only works for simple comparisons. If the customer needs to do any arithmetics on the integer that was casted from string, then solution 1 with the view v2 followed by a more specific query would work better."