conditional In-List

I am trying to do a 'conditional In-List'. Here is what I mean.

• BigDataTable create table bdt ( "C1" VARCHAR2(2 BYTE) not null, "C2" VARCHAR2(2 BYTE) not null, "C3" VARCHAR2(2 BYTE) not null, "C4" VARCHAR2(2 BYTE) not null ) ;

insert into bdt
select 'a' as c1,'b' as c2,'c' as c3,'d' as c4 from dual union all
select 'aa' as c1,'bb' as c2,'cc' as c3,'dd' as c4 from dual union all
select 'a1' as c1,'b' as c2,'c' as c3,'d' as c4 from dual union all
select 'a2' as c1,'bb' as c2,'cc' as c3,'dd' as c4 from dual union all
select 'a3' as c1,'b1' as c2,'c1' as c3,'d1' as c4 from dual union all
select 'a4' as c1,'b1' as c2,'c1' as c3,'d1' as c4 from dual union all
select 'a5' as c1,'b2' as c2,'c2' as c3,'d2' as c4 from dual union all
select 'a6' as c1,'bb' as c2,'cc' as c3,'dd' as c4 from dual union all
select 'a7' as c1,'b3' as c2,'c3' as c3,'d3' as c4 from dual;

• lookup table used to select what rows are candidates from c2 create table lkup_c2( la varchar2(2) not null, lb varchar2(2) not null, lc varchar2(2) not null);

insert into lkup_c2
select 'e','e','bb' from dual
union all
select 'e','e','b1' from dual
union all
select 'e','e','b' from dual
union all
select 'f','e','x1' from dual
union all
select 'f','e','x' from dual;

• lookup table 2 used to select what rows are candidates from c3 create table lkup_c3( la varchar2(2) not null, lb varchar2(2) not null, lc varchar2(2) not null);

insert into lkup_c3
select 'e','e','cc' from dual
union all
select 'e','e','c1' from dual
union all
select 'e','e','c' from dual
union all
select 'f','e','x1' from dual
union all
select 'f','e','x' from dual;

• a simple in-list we get 7 rows select * from bdt where c2 in (select lc from lkup_c2 where la = 'e');
• another simple in-list returns no rows select * from bdt where c2 in (select lc from lkup_c2 where la = 'f');
• but in my situation I need both lookup tables with a 'AND' between them
• like this select * from bdt where c2 in (select lc from lkup_c2 where la = 'e') and c3 in (select lc from lkup_c3 where la = 'e');
• this works fine, my problem is the if table lkup_c2 returns no rows it
• should be ignored for example: select * from bdt where c2 in (select lc from lkup_c2 where la = 'f') and c3 in (select lc from lkup_c3 where la = 'e');
• effictively the query should act like select * from bdt where c3 in (select lc from lkup_c3 where la = 'e');

In short if either of the in lists returns no rows it should be ignored. I am really stuck on how to do this. The best idea I have come up with is to dynamiclly building the query as a string. I would first check if the lookup tables return a value then put it in the string. Then use an OPEN-FOR statement with
a cursor variable. The values of column la in both tables is provided at run time by the calling application.

Thanks Received on Sat Apr 21 2007 - 11:26:52 CDT