Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

IN clause with multivalue tuple with NULLS?

538048Jul 27 2007 — edited Jul 27 2007
I have a table somewhat like this:

mytable
{
pk NUMBER(20) NOT NULL,
ak1.1 NUMBER(20) NOT NULL,
ak1.2 NUMBER(20) NOT NULL,
ak1.3 VARCHAR2(255) NULL,
otherstring VARCHAR2(255) NULL
}

So I have a 3 part alternate key containing 2 long values and one string the longs are not null but the string value could be null.

I want to retrieve from this table like this:

select pk, ak1.1, ak1.2, ak1.3 from mytable where (ak1.1, ak1.2, ak1.3) IN ((100, 500, 'string'), (300, 500, NULL));

The problem I am having is the most of the values in the table have null in AK1.3 and it appears that the keyword NULL in the later half of the IN clause does not work. Is there some trick to be able to have it match alternate keys where one of the values is null using this method? I have tried NULL and IS NULL neither worked I tried matching one of these tuples in the following manner:

select pk, ak1.1, ak1.2, ak1.3 from mytable where ak1.1 = 500 and ak1.2 = 600 and ak1.3 IS NULL;

That matched so I am fairly certain the value is actually null but I need to be able to fetch it with an in clause because I am matching a list that could actually be hundreds of tuples so this second approach isn't very practical. Any suggestions appreciated.

Thanks!
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 24 2007
Added on Jul 27 2007
2 comments
647 views