Re: conditional In-List

Oxnard schrieb:
> 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 = 'e')) AND NOT EXISTS (select lc from lkup_c3 where la = 'e')
OR
(c3 in (select lc from lkup_c3 where la = 'e')) AND NOT EXISTS (select lc from lkup_c2 where la = 'e')
OR
(c2 in (select lc from lkup_c2 where la = 'e') AND c3 in (select lc from lkup_c3 where la = 'e'))

Best regards

Maxim Received on Sat Apr 21 2007 - 17:19:41 CDT