"IN" in Select - 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

SQL Server 2000 Forums

SQL Server Development (2000)

"IN" in Select

Author

Topic

asutoshv
Starting Member

2 Posts

Posted - 2005-08-02 : 04:15:11

I've a problem in a query, it is performing in Oracle but not working in Sql:

select * from tabA a where (a.sr_no, a.nm) in ((1,'a'),(2,'b'),(3,'c')) ;
tabA is :
sr_no number(3),
nm varchar(10)

If you have any solution or alternate, plz inform asap.
Thank You.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-02 : 04:17:29

Try this

select * from tabA a where a.sr_no in (1,2,3)
and a.nm in ('a','b','c')

Madhivanan

Failing to plan is Planning to fail

asutoshv
Starting Member

2 Posts

Posted - 2005-08-02 : 04:21:40

Sorry, but it wont work like that.
my question is i've to select records like :
select * from tabA where (a.sr_no = 1 and a.nm = 'a') or (a.sr_no = 2 and a.nm = 'b') or(a.sr_no = 3 and a.nm = 'c')

so instead of writing these many 'or' and 'and's can it work like :
select * from tabA a where (a.sr_no, a.nm) in ((1,'a'),(2,'b'),(3,'c')) ;

Kristen
Test

22859 Posts

Posted - 2005-08-02 : 04:39:01

That style of IN is not supported in SQL and I can't think of an easy workaround if you are using Dynamic SQL.

You could put the values into a temporary table (perhaps passing them as a delimited string, and "splitting" that into a temporary table?) along the lines of


SELECT [num]=1,[letter]='a'
INTO #MyTempTable
UNION ALL SELECT 2,'b'
UNION ALL SELECT 3,'c'

select * 
from tabA a
     JOIN #MyTempTable T
         ON T.num = a.sr_no
        AND T.letter = a.nm

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-02 : 04:53:28

If your data in the table are as follows

sr_no nm
1 'a'
2 'b'
3 'c'

No need of checking the nm value
Can you post some sample data?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources