Bizarre Error converting data type varchar to float

Asked Jan 10 '16 at 16:53

Active Jan 10 '16 at 16:55

Viewed 27 times

When I select from a view, I get an error:

Error converting data type varchar to float.

Query:

select cast(myNumVal as float) myFloatVal 
from uvw_myView

When I execute it this, however, it works, no errors

select 
    cast(myNumVal as float) myFloatVal,  
    CASE 
        WHEN ISNUMERIC(myNumVal + 'e0') = 0 THEN myNumVal 
        ELSE null 
    END AS MyVarchar
from uvw_myView

The two queries are identical, with the exception of the additional column in the latter. Can somebody explain why this is happening?

edited Jan 10 '16 at 16:55

marc_s

732,580
175
1,330
1,459

asked Jan 10 '16 at 16:53

Tom McDonald

1,532
2
18
37

Does you real query have a `where` clause? – Martin Smith Jan 10 '16 at 16:58
Yes, it does have a simple where clause, e.g., where myCategory = 'x' – Tom McDonald Jan 10 '16 at 17:04
You have no guarantee whether or not the cast will happen before or after the where. In one plan you happen to be lucky in the other not. – Martin Smith Jan 10 '16 at 17:09

Bizarre Error converting data type varchar to float

0 Answers0