CODEWITHSUNDEEP
sql-serversubquery
sanchin
sanchin

Reputation: 117

Sub query returning all values and then applying where condition

I am having an issue with a query only in one particular environment/database even if the data is almost similar. Here is the simplified scenario:

Table A - One column - Id (long)

Id
1
2
3

Table B - Two columns - value(varchar) and field2(varchar)

  Value Field2
1)abc   NotKey
2)Test  NotKey
3)1     Key
4)1.56  NotKey

When I run the query

select * from table a
where id in(select value from table b where Field2 = 'Key')

I get the error

Result: Conversion failed when converting the varchar value 'abc' (earlier I had this value erraneously as 'NotKey') to data type int.

on one database. In three other databases, the value returns correctly as "1".

I am using SQL Server 2008. What might be the issue here?

Upvotes: 0

Views: 56

Answers (2)

jtimperley
jtimperley

Reputation: 2544

You must have a record in this instance that doesn't follow the same pattern as before. Try running the following query to find your bad data. You could either fix the record or add a numeric check to the query you're using.

select *
from table
where Field2 = 'Key'
    and (ISNUMERIC(Value) = 0
        OR CHARINDEX('.', Value) > 0);

Filtered query:

select *
from table a
where id in
(
    select value
    from table b
    where Field2 = 'Key'
        and ISNUMERIC(value) = 1
        and CHARINDEX('.', Value) = 0
);

Upvotes: 1

Mathias F
Mathias F

Reputation: 15891

You gave the wrong filter for the filter that leads to the error.

The errror only happens when you select:

select * from tablea
where id in(select value from tableb where Field2 = 'NotKey')

You have to cast one of the columns 

select * from tablea
where cast( id as nvarchar(20)) in(select value from tableb where Field2 = 'NotKey')

http://sqlfiddle.com/#!6/e9223/23

Upvotes: 1

Related Questions