CODEWITHSUNDEEP
sqlsql-server
Vincent
Vincent

Reputation: 141

SQL : Error converting data type varchar to numeric

I have met a very strange problem. There are two sql queries:

Q1:

SELECT *
FROM tbl_1
WHERE ID IN (SELECT TargetID
             FROM tbl_2 
             WHERE PeopleID = 'AAA') 
AND ID = 49

Q2:

SELECT *
FROM tbl_1
WHERE ID IN (SELECT TargetID
             FROM tbl_2 
             WHERE PeopleID = 'BBB') 
AND ID = 49

We could find these two queries are identical except for PeopleID. But their result were very difference. For Q1, we got "Error converting data type varchar to numeric"; And for Q2, it ran well. I was confused!

There are some infomation that may be useful.

tbl_1  ID       numeric(18,0) not null

tbl_2  TargetID varchar(50)   not null

and every TargetID in Q1 or Q2 could pass the IsNumeric test (namely, IsNumeric(TargetID) = 1)

another information: if we comment the line AND ID = 49 and every query run well

thx for your help

Upvotes: 1

Views: 19701

Answers (3)

Richard Mohammed
Richard Mohammed

Reputation: 84

@Vincent have you checked if there is a space type character (or currency characters) before or after the TargetID when PeopleID = 'BBB'? You will get this error message if there's a Tab or CrLf etc before or after the TargetID. A regular whitespace however shouldn't give an error.

Upvotes: 0

Ross Bradbury
Ross Bradbury

Reputation: 605

How about using EXISTS?

SELECT *
FROM tbl_1
WHERE EXISTS (SELECT *
             FROM tbl_2 
             WHERE PeopleID = 'BBB'
             AND tbl_2.TargetID = tbl_1.ID) 
AND ID = 49

Upvotes: 0

Don Dickinson
Don Dickinson

Reputation: 6258

i would try doing something like this instead:

SELECT *
FROM tbl_1
WHERE convert(varchar(50), ID) IN (SELECT TargetID
             FROM tbl_2 
             WHERE PeopleID = 'BBB') 
AND ID = 49

since the ID is converted to a varchar, it should work ok.

Upvotes: 2

Related Questions