Passing multiple parametes in Where-In SQL- Oracle

Question

I am trying to write a query passing three variables together:-

select 
a,b,c,*
from table1 where
(a,b,c) in (('1','2','3'),('4','5','6'));

This gives me results when none of the values are null.

However when I try to pass atleast one of them as null/blank it gives me invalid relational operator error:-

select 
a,b,c,*
from table1 where
(a,b,c) in (('1','2',null),('4','5',''));

  [Error Code: 920, SQL State: 42000]  ORA-00920: invalid relational operator

Could you please help me with a workaround to handle this? I can use union of three different queries but that is error prone with huge data to query.

Thanks,

Maria Nazari · Accepted Answer · 2020-02-28 16:48:39Z

1

That's because you giving it options to only pull non-null values, try something like this

select 
a,b,c,*
from table1 where
(a in ('1', '4')) or (b in ('2','5')) or (c in ('3','6'));

answered Feb 28, 2020 at 16:48

Maria Nazari

6881 gold badge9 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

RLearner Over a year ago

Thank you for the answer.However this is not giving me desired results.It is replacing the null/blank with other values in the in clause.

Littlefoot Over a year ago

On the contrary, this query seems to do exactly what you wanted. Where and what is replacing null/blank? Your second query would just be ... or c is null

Collectives™ on Stack Overflow

Passing multiple parametes in Where-In SQL- Oracle

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related