IN clause with multivalue tuple with NULLS?
538048Jul 27 2007 — edited Jul 27 2007I 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!