need query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

need query

Author

Topic

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-30 : 17:32:52

select cse from cse_list where id=1,2
I know it is wrong .. How to use , in the condition value

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-09-30 : 17:37:55

where [id] IN (1,2)

Be One with the Optimizer
TG

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-30 : 17:41:00

select cse from cse_list where id in (1,2)
This query works..
select cse from cse_list where id in( select cse from tbl_user where login_name='wer')
While executing this query i got this error

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-30 : 17:41:51

select cse from tbl_user where login_name='wer' ..This query generates this output
1,2,3,4,5,6,7,8

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-09-30 : 17:44:28

It appears that the CSE column is varchar, while the ID column is int, and there's a CSE value that cannot be converted.

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-30 : 17:48:54

How to solve this problem...

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-09-30 : 17:52:55

The best solution is to modify the tables so that those columns are the same data type. If that's not an option then you'll have to modify your WHERE clause to ignore non-numeric CSE values:

select cse from cse_list
where id in( select cse from tbl_user where login_name='wer' and cse not like '%[^0-9]%')

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-09-30 : 17:56:18

agreed about the best solution. Another kluge could be to convert the INTs to varchar before comparing them:


select cl.cse 
from   cse_list cl
join   tbl_user u on u.cse = convert(varchar(max), cl.id)
where  u.login_name='wer'

Be One with the Optimizer
TG

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-30 : 17:57:12

What is the meaning for this "cse not like '%[^0-9]%"

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-30 : 18:04:27

Above two query generate same answer..
Only the column name selected by the query

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-09-30 : 18:10:54

quote:
Originally posted by jafrywilson

What is the meaning for this "cse not like '%[^0-9]%"

LIKE matches character patterns. The '%[^0-9]%' pattern matches any non-digit character, and NOT LIKE '%[^0-9]%' will remove any row that contains a non-numeric character in CSE.

Books Online has plenty of documentation and examples on LIKE and similar functions.

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-30 : 18:12:52

Tnx Rob..

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-30 : 18:21:34

How to convert varchar to numeric datatype

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-09-30 : 18:24:10

Check out the CONVERT() function in Books Online.

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-30 : 19:19:25

I changed the table field datatype...And tired this query

declare @sq varchar(50)
select @sq= cse from tbl_user where login_name='teptronics'
select cse from cse_list where id in (@sq)

while printing @sq i got 1,2,3,4,5,6,7,8.. But executing the above query i dint get the result

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-30 : 19:20:29

while executing select cse from cse_list where id in (1,2,3,4,5,6,7,8)
I got the correct output...

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-09-30 : 19:58:40

in one case you are specifying an array of integers. In the other you are specifying a single string value:

where id in (1,2,3,4,5,6,7,8)
vs.
where id in ('1,2,3,4,5,6,7,8')

Read through these topics in this thread:

CSV / Splitting delimited lists
WHERE IN @MyCSV
Article: Passing a CSV or Array to a Stored Procedure (NC)
Best split functions

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources