Re: conditional In-List

"Ana C. Dent" <anacedent_at_hotmail.com> wrote in message news:PVrWh.153459$g24.24121_at_newsfe12.phx...
> "Oxnard" <oxnard_at_carolina.rr.com> wrote in
> news:462a3b5e$0$5775$4c368faf_at_roadrunner.com:
>
>> 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
>>
>>
>>
>>
>
> select * from bdt
> where
> c2 in (select lc from lkup_c2 where la = 'f')
> or
> c3 in (select lc from lkup_c3 where la = 'e');
>

Unfortunately an OR operator would not work. The rule is If the in list exists then it should be ANDed together with the next in list. Received on Sat Apr 21 2007 - 14:45:28 CDT