Multi-column IN clause

Question

0.00/5 (No votes)

See more:

Any ideas on how to do a multi-column IN clause? Note that the comparison set is coming from application memory, not a subquery in Oracle. So, for example, I have two arrays, IDs() and Names(), and I want to get something like this:

SQL

SELECT * FROM MY_TABLE WHERE (ID, NAME) NOT IN (:IDs, :Names)

Most common error I've been getting is "invalid relational operator".

Open to other suggestions. I'd like to avoid building an ugly query like

SQL

SELECT * FROM MY_TABLE WHERE (ID, NAME) NOT IN ((ID1,Name1), (ID2,Name2), ...)

or even worse,

SQL

SELECT * FROM MY_TABLE WHERE (ID || NAME) NOT IN (:IDNames)

Posted 17-Apr-12 2:43am

woopsydoozy

Add a Solution

2 solutions

Add a Solution

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

gopi1987 · Answer 1 · 2012-04-17T03:25:00

Previously you have to select ids which you want to neglet/omit then push that ids into temp table and then filter records using temp table ids

here sample code

SQL

DECLARE @TempTable TABLE (Ids int)

insert into @TempTable(Ids)
select StudentIdid from StudentTable where StudentId<10
  
select StudentId,StudentName from StudentTable where StudentId not in (select Ids from @TempTable)

i hope you understand...

djrocks0101 · Answer 2 · 2012-04-17T03:51:00

Solution 2

You can use dynamic views
click here2

Posted 17-Apr-12 3:51am

djrocks0101

Multi-column IN clause

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Multi-column IN clause

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us