October 9, 2009 at 6:07 am
Here's what I'm trying to do. I have two tables in my database.The first table contains an integer column say ClientID.The second table contain a varchar column say X.the second table data of Column X is same as that of the first table ClientID.The only difference is that the second table contains ClientID seperated by Comma.
Now when i am executing the following query :-
select * from TAble1 where ClientID in (select X from TAble2)
i am getting the following error :-
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '444,411' to data type int.
here 444 and 411 are two clientID.
IS there any other way to get the desired result?
Kindly Help!
October 9, 2009 at 6:28 am
SQL's trying to do an implicit conversion of the varchar field and failing. '441,443' cannot be converted because of the comma. This will work, but it could cause significant performance problems. Why are the ClientIDs lumped into one field like that? They should be separated out into their own fields so you don't have to jump through hoops to get this type of query to work, but it's probably too late to fix it.
create TABLE TableA(Clientid INT)
Create Table TableB (X varchar(10))
insert into TableA values(441)
insert into TableB values ('441,443')
select a.*
from TableA a
inner join TableB b
on x like '%' + cast(a.ClientID as varchar) + '%'
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 9, 2009 at 6:41 am
The Clientid you took for the example worked fine. but if the clientid 1,4 ... are also present in the table. the resultset also contain the details of these id. is there any way to get only the desired id data.
October 9, 2009 at 6:47 am
You were looking for 441, but your example showed 444,411 which won't match anyway. I added these two to the original and 2 of the 3 showed up, which is correct
insert into TableB values ('444,411')
insert into TableB values ('444,441')
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 9, 2009 at 7:02 am
yes! sorry my mistake .
but the final resultset also contain the data for client id 's:
1
4
11
44
apart from what was needed.Is there any way to get the exact resultset
October 9, 2009 at 7:37 am
Are there always exactly two records seperated by a comma? If so, you could use something like this:
select a.*
from TableA a
inner join (select * from
(select cast(left(X,charindex(',',X)-1) as int) as val1, cast(right(X,len(X)-len(left(X,charindex(',',X)))) as int) as val2 from TableB) pvt
UNPIVOT (ClientID for val in ([val1],[val2])) as Unpvt) b
on b.ClientID=a.clientID
I would suggest fixing the data structure as SQL is not good at this kind of manipulation and it's a nightmate to maintain referential integrity.
October 9, 2009 at 8:24 am
There can be more than 2 clientid seperated by coma.
Is there a workaround using while loop, to find the left or right ..??
October 9, 2009 at 8:38 am
Right here there is.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply