Strange error for filtering a CAST data wth WHERE clause

Question

Post reply

Strange error for filtering a CAST data wth WHERE clause

ST-319735

SSC Veteran

Points: 259
More actions
April 16, 2006 at 7:15 am

#167697

Hello,
I have a 'Data' table like this:
'data-id': int, PK
'data': varchar(20)
I want to select some of the rows in it which the data in 'data' column is integer, and is bigger than a number, let say 5.
My SQL used in my vb.net project is:
SELECT Expr1
FROM (SELECT [data-id], CAST(data AS int) AS Expr1
FROM Data
WHERE [data-id] IN
(SELECT [data-id] FROM Data WHERE (isnumeric(data) = 1) AND data IS NOT NULL))
DERIVEDTBL
WHERE (Expr1 > 5)
which gives me error message: 'syntex error converting varchar value "abc" to a column of datatype int' in Visual studio.net 03.(There is a row in 'Data' with 'data' value = 'abc' and another 2 rows with 'data' = '1' and '11')
But if I cut the last 'WHERE' clause, the sql run successfully.
May I ask why? And may anyone help?
Thx!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

[KH] SSCommitted Points: 1908 More actions · Answer 1

change the derived table to

SELECT [data-id], CAST(data AS int) AS Expr1

FROM Data

WHEREisnumeric(data) = 1

anddata is not null

ST-319735 SSC Veteran Points: 259 More actions · Answer 2

First, thx for your help!

Do you mean this:

SELECT Expr1

FROM (SELECT [data-id], CAST(data AS int) AS Expr1

FROM Data

WHERE ISNUMERIC(data) = 1

AND data IS NOT NULL) DERIVEDTBL

WHERE (Expr1 > 5)

But sorry, the same error appears again.

Sergiy SSC Guru Points: 110209 More actions · Answer 3

Optimiser choses the fastest way to execute query. That's why it applyes strongest condition first to eliminate maximum number of rows from resultset.

" > 5" is stronger that "ISMUMERIC", that's why it goes first and causes error.

If you want to filter out not numeric results by derived table you need to apply some grouping for this. But it will slow down your query.

In fact you don't need DT at all:

SELECT CASE WHEN ISNUMERIC(data) = 1 THEN CAST(data AS int) ELSE NULL END AS Expr1

FROM Data

WHERE CASE WHEN ISNUMERIC(data) = 1 THEN CAST(data AS int) ELSE NULL END > 5

Actually you need to have more complex check because FLOAT value will give you ISNUMERIC(data) = 1 but cause error on attempt to convert to int.

_____________
Code for TallyGenerator

ST-319735 SSC Veteran Points: 259 More actions · Answer 4

Thx very much!

But after some search in google, I found that isnumeric() return true for data like "- 1", which can't be convert to numeric data.

Instead, now I convert and filter data in my VB.NET program.

By the way, thx again.

RyanRandall SSChampion Points: 13623 More actions · Answer 5

Here's some info on the ISNUMERIC function, and a work-around...

http://aspfaq.com/show.asp?id=2390

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

Strange error for filtering a CAST data wth WHERE clause

Cookies on SQLServerCentral