Click here to Skip to main content
15,936,565 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Dear sir i have a problem regarding to sql query please help me


ex:

SQL
select schchargename,amount from tbschcharge where schchargeid in(select stdchrgs from tbstdadmcharges where stdadmno=4569)


schchargeid is int data type first table

stdchrgs is varchar data type in second table

this give me error cannot convert varchar to int

this qyery give result: select schchargename,amount from tbschcharge where schchargeid in(1,2,4,7)



I want this output:

Security 2500
Computer Fees 300
Building Fund 200
Tution Fees 300

Randeep Chauhan
Posted
Updated 12-May-11 2:14am
v2

This should work:
SQL
DECLARE @list AS varchar(50);
SET @list = (SELECT stdchrgs FROM tbstdadmcharges WHERE stdadmno=4569)
SELECT schchargename,amount
FROM tbschcharge
WHERE PATINDEX(@list, LTRIM(RTRIM(CONVERT(varchar(10), schchargeid))) + ',') > 0 OR
      PATINDEX(@list, ',' + LTRIM(RTRIM(CONVERT(varchar(10), schchargeid)))) > 0


It converts the varchar in column stdchrgs to integers.

Note: This is only a workaround as integer values stored in a varchar column are an indication that something went wrong in the DB design.

Best Regards,

-MRB
 
Share this answer
 
v4
Comments
Randeep Chauhan 12-May-11 8:33am    
Thanks for reply sir but my problem is that my value (stdchrgs) contain data like (1,2,3,4,5) so its not convergt into integer
is there any technique actualy i'm use to it for in query
like : select * from <Table> where in(stchrgs)

but it is in varchar

so, plz hlp me
Manfred Rudolf Bihy 12-May-11 8:50am    
I updated my solution with something that should work for you. Please try it out.

I still yould prefer it if you would use a relational DB in the way it was meant to be used. Storing a list of integers as a varchar is not the way to do it. You should really think about remodelling your DB design.
Marc A. Brown 12-May-11 8:56am    
Amen to that.
use below script, this should work

select schchargename,amount from tbschcharge where CAST(schchargeid as varchar) in(select stdchrgs from tbstdadmcharges where stdadmno=4569)

OR
select schchargename,amount from tbschcharge where Convert(varchar,schchargeid ) in(select stdchrgs from tbstdadmcharges where stdadmno=4569)



above i converted INT value to VARCHAR , bcoz INT can not convert to VARCHAR due to of size.
 
Share this answer
 
v2
Comments
[no name] 12-May-11 9:05am    
my 4
Manfred Rudolf Bihy 12-May-11 9:21am    
I'm sorry to say that you solution will not work:
1. Because OP said that table 2 contains "(1,4,5,7)" as a value in column stdchrgs
2. IN will not work in the way indicated that is what PATINDEX is there for.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900