Re: conditional In-List

"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'); Received on Sat Apr 21 2007 - 12:33:35 CDT