Thanks for the question, Amine.

Asked: December 05, 2014 - 9:12 pm UTC

Last updated: December 09, 2014 - 3:54 pm UTC

Version: 11.2.0.3

Viewed 1000+ times

You Asked

Hi Tom,
I have this table

create table test
(
  id  int ,
  x   int
);

insert into test values (1,1);
insert into test values (1,2);
insert into test values (1,3);
insert into test values (1,4);

insert into test values (2,1);
insert into test values (2,2);
insert into test values (2,3);

insert into test values (3,1);
insert into test values (3,2);

The requirement is : we want to get ids that have exactly x in (1,2).
In my case, the query should return id = 3 only.

A query that responds to this can be :

select distinct id
from test
where id in (select id from test where x = 1)
and id in (select id from test where x = 2)
and id not in (select id from test where x not in (1,2));

Now I want to write a generic query. Imagine that the requirement change.
For example, we want to get ids that have exactly x in (1,2,3), then I have to change my query :

select distinct id
from test
where id in (select id from test where x = 1)
and id in (select id from test where x = 2)
and id in (select id from test where x = 3)
and id not in (select id from test where x not in (1,2,3));

I imagine a table like this

create table req
(
  req varchar2(100) ,
  x   int
)
/

insert into req valus ('req1', 1);
insert into req valus ('req1', 2);

insert into req valus ('req2', 1);
insert into req valus ('req2', 2);
insert into req valus ('req2', 3);

to be used in the generic query.
How can we write this query ?

Thanks in advance,

Amine

and Tom said...

NOTE: updated 9-dec-2014 to correct a bug in the original implemenation:

ops$tkyte%ORA11GR2> select * from test;

        ID          X
---------- ----------
         1          1
         1          2
         1          3
         1          4
         2          1
         2          2
         2          3
         3          1
         3          2
         4          1
         4          2
         4

12 rows selected.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> variable txt varchar2(20)
ops$tkyte%ORA11GR2> exec :txt := '1'

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> with data
  2  as
  3  (
  4  select
  5    trim( substr (txt,
  6          instr (txt, ',', 1, level  ) + 1,
  7          instr (txt, ',', 1, level+1)
  8             - instr (txt, ',', 1, level) -1 ) )
  9      as token
 10     from (select ','||:txt||',' txt
 11             from dual)
 12   connect by level <=
 13      length(:txt)-length(replace(:txt,',',''))+1
 14   )
 15  select id
 16    from test, data
 17   where id in (select id from test where x in (select * from data) )
 18     and test.x = data.token(+)
 19    group by id
 20   having count(distinct data.token ) = (select count(*) from data)
 21      and count(data.token) = count(*)
 22  /

no rows selected

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> exec :txt := '1,2'

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> /

        ID
----------
         3

ops$tkyte%ORA11GR2> exec :txt := '1,2,3'

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> /

        ID
----------
         2

ops$tkyte%ORA11GR2> exec :txt := '1,2,3,4'

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> /

        ID
----------
         1

ops$tkyte%ORA11GR2> exec :txt := '1,2,3,4,5'

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> /

no rows selected

the "with data" bit lets us select * from a string, turning a delimited string into a set http://asktom.oracle.com/Misc/varying-in-lists.html

We get all of the rows for all of the ID's such that they have an X value in that string, then using simple aggregation - we look for ID's that have a distinct cardinality of X equal to the number of elements in the string...

Rating

(8 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Another one

Michel Cadot, December 08, 2014 - 10:17 am UTC

http://www.orafaq.com/forum/mv/msg/195427/628380/#msg_628380

SQL> def list=1
SQL> select id
  2  from test
  3  group by id
  4  having count(distinct x)=(select count(*) from table(sys.odcinumberlist(&list)))
  5  /

no rows selected

SQL> def list=1,2
SQL> /
        ID
----------
         3

1 row selected.

SQL> def list=1,2,3
SQL> /
        ID
----------
         2

1 row selected.

SQL> def list=1,2,3,4
SQL> /
        ID
----------
         1

1 row selected.

SQL> def list=1,2,3,4,5
SQL> /

no rows selected

Regards
Michel

December 08, 2014 - 3:21 pm UTC

use binds!!!!!!!!!!!!!!!!!!!!!!!!!!

it does not however answer the question. It gets the wrong answer.... see above, ,it is trickier than you make it to be.

if ID=5 had X values = 100,200, you would return that. but you should not. It is not enough to count the number of distinct X's by ID, you need to ensure they are in the set of X's you are looking for and that that ID doesn't have any extra X values...




ops$tkyte%ORA11GR2> insert into test values (5,100);

1 row created.

ops$tkyte%ORA11GR2> insert into test values (5,200);

1 row created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> def list=1,2
ops$tkyte%ORA11GR2> select id
  2    from test
  3    group by id
  4    having count(distinct x)=(select count(*) from table(sys.odcinumberlist(&list)))
  5  /
old   4:   having count(distinct x)=(select count(*) from table(sys.odcinumberlist(&list)))
new   4:   having count(distinct x)=(select count(*) from table(sys.odcinumberlist(1,2)))

        ID
----------
         5
         3



ops$tkyte%ORA11GR2> exec :txt := '1,2'

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> /

        ID
----------
         3

Amine, December 08, 2014 - 11:25 am UTC

Thanks for the reply.

Suppose we add these 2 rows :

insert into test values (4,1);
insert into test values (4,6);

SQL> var txt varchar2(100);
SQL> exec :txt := '1,2';

PL/SQL procedure successfully completed.

SQL> 
SQL> select *
  2  from
  3  (
  4   with data
  5   as
  6   (
  7    select
  8    trim( substr (txt,
  9    instr (txt, ',', 1, level  ) + 1,
 10    instr (txt, ',', 1, level+1)
 11    - instr (txt, ',', 1, level) -1 ) )
 12    as token
 13    from (select ','||:txt||',' txt
 14    from dual)
 15    connect by level <=
 16    length(:txt)-length(replace(:txt,',',''))+1
 17   )
 18   select id
 19   from test
 20   where id in (select id from test where x in (select * from data) )
 21   group by id
 22   having count(distinct x) = (select count(*) from data)
 23  )
 24  /

       ID
---------
        3
        4

id 4 should not appear.

December 08, 2014 - 3:31 pm UTC

good catch.

We need to join back to data to pick up the token in an outer join.

then we can make sure the distinct X values are all there AND that there are no extra rows with NULLs in the token field. That will ensure that an ID has at least as make rows as data and at most as many rows as data and each row the ID has has a mate in data

ops$tkyte%ORA11GR2> variable txt varchar2(20)
ops$tkyte%ORA11GR2> exec :txt := '1'

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> with data
  2  as
  3  (
  4  select
  5    trim( substr (txt,
  6          instr (txt, ',', 1, level  ) + 1,
  7          instr (txt, ',', 1, level+1)
  8             - instr (txt, ',', 1, level) -1 ) )
  9      as token
 10     from (select ','||:txt||',' txt
 11             from dual)
 12   connect by level <=
 13      length(:txt)-length(replace(:txt,',',''))+1
 14   )
 15  select id
 16    from test, data
 17   where id in (select id from test where x in (select * from data) )
 18     and test.x = data.token(+)
 19    group by id
 20   having count(distinct data.token ) = (select count(*) from data)
 21      and count(data.token) = count(*)
 22  /

no rows selected

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> exec :txt := '1,2'

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> /

        ID
----------
         3

ops$tkyte%ORA11GR2> exec :txt := '1,2,3'

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> /

        ID
----------
         2

ops$tkyte%ORA11GR2> exec :txt := '1,2,3,4'

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> /

        ID
----------
         1

ops$tkyte%ORA11GR2> exec :txt := '1,2,3,4,5'

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> /

no rows selected

A reader, December 08, 2014 - 3:59 pm UTC

Wouldn't this work?

select *
from (
select id, listagg(x, ',') within group (order by x) x
from test
group by id
) where x = '1,2';

December 09, 2014 - 2:58 pm UTC

yes and no.

It would make the assumption first that the input has to be perfect - '1, 2' would not work.

It would also tend to full scan - which you probably don't want.

simpler

Sokrates, December 08, 2014 - 4:10 pm UTC

sokrates > variable txt varchar2(20)
sokrates > exec :txt := '1'

PL/SQL procedure successfully completed.

sokrates > select id
sokrates > from test
sokrates > group by id
sokrates > having listagg(x, ',') within group(order by x) = :txt
sokrates > /

no rows selected

sokrates > exec :txt := '1,2'

PL/SQL procedure successfully completed.

sokrates > /

        ID
----------
         3

sokrates > exec :txt := '1,2,3'

PL/SQL procedure successfully completed.

sokrates > /

        ID
----------
         2

sokrates > exec :txt := '1,2,3,4'

PL/SQL procedure successfully completed.

sokrates > /

        ID
----------
         1

sokrates > exec :txt := '1,2,3,4,5'

PL/SQL procedure successfully completed.

sokrates > /

no rows selected

December 09, 2014 - 3:01 pm UTC

no indexes can be used here...

In general, I would guess that the use of indexes would be beneficial to this query - hence the purposeful use of "in"

however, if a full scan was desirable (eg: most of the ID's have the #1 in them and you look for #1 all of the time - full scan would be desirable), this would be good

Collection based solution

Solomon Yakobson, December 08, 2014 - 6:50 pm UTC

Hi Tom,

Your solution doesn't account for NULL values:

SQL> insert into test values(3,null);

1 row created.

SQL> variable txt varchar2(20)
SQL> exec :txt := '1,2'

PL/SQL procedure successfully completed.

SQL> with data
  2  as
  3  (
  4  select
  5    trim( substr (txt,
  6          instr (txt, ',', 1, level  ) + 1,
  7          instr (txt, ',', 1, level+1)
  8             - instr (txt, ',', 1, level) -1 ) )
  9      as token
 10     from (select ','||:txt||',' txt
 11             from dual)
 12   connect by level <=
 13      length(:txt)-length(replace(:txt,',',''))+1
 14   )
 15  select id
 16    from test
 17   where id in (select id from test where x in (select * from data) )
 18   group by id
 19  having count(distinct x) = (select count(*) from data)
 20  /

        ID
----------
         3

SQL> 

SQL> select  id
  2    from  test
  3    group by id
  4    having set(cast(collect(x) as ku$_objnumset)) = ku$_objnumset(1,2)
  5  /

        ID
----------
         3

SQL> 

So I'll also assume NULLs are ignored. Below is collection-based solution:

SQL> select  id
  2    from  test
  3    group by id
  4    having set(cast(collect(x) as ku$_objnumset)) = ku$_objnumset(1,2)
  5  /

        ID
----------
         3

SQL>

And if we need to consider NULLs:

SQL> select  id
  2    from  test
  3    group by id
  4    having set(cast(collect(x) as ku$_objnumset)) = ku$_objnumset(1,2)
  5       and count(*) = count(x)
  6  /

no rows selected

SQL> delete test where x is null;

1 row deleted.

SQL> select  id
  2    from  test
  3    group by id
  4    having set(cast(collect(x) as ku$_objnumset)) = ku$_objnumset(1,2)
  5       and count(*) = count(x)
  6  /

        ID
----------
         3

SQL> 

SY.

December 09, 2014 - 3:35 pm UTC

ku$_objnumset is undocumented and unsafe, please do not use it -especially when there is a documented safe one to use.

sys.odciNumberList is documented and safe

however, you do not use bind variables, and even if you did - you do not use them in a way that permits cursors to be shared.

and even if you did - you do not allow for indexes to be used (and I really presume that indexes should be used here)

the original query had a bug in it - we fixed that. the updated select (above) deals with this already:

ops$tkyte%ORA11GR2> select * from test;

        ID          X
---------- ----------
         1          1
         1          2
         1          3
         1          4
         2          1
         2          2
         2          3
         3          1
         3          2
         3                        <<<<<<<<==== null row
         4          1
         4          6

12 rows selected.

ops$tkyte%ORA11GR2> variable txt varchar2(20)
ops$tkyte%ORA11GR2> exec :txt := '1'

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> with data
  2  as
  3  (
  4  select
  5    trim( substr (txt,
  6          instr (txt, ',', 1, level  ) + 1,
  7          instr (txt, ',', 1, level+1)
  8             - instr (txt, ',', 1, level) -1 ) )
  9      as token
 10     from (select ','||:txt||',' txt
 11             from dual)
 12   connect by level <=
 13      length(:txt)-length(replace(:txt,',',''))+1
 14   )
 15  select id
 16    from test, data
 17   where id in (select id from test where x in (select * from data) )
 18     and test.x = data.token(+)
 19    group by id
 20   having count(distinct data.token ) = (select count(*) from data)
 21      and count(data.token) = count(*)
 22  /

no rows selected

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> exec :txt := '1,2'

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> /

no rows selected

ops$tkyte%ORA11GR2> exec :txt := '1,2,3'

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> /

        ID
----------
         2

ops$tkyte%ORA11GR2> exec :txt := '1,2,3,4'

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> /

        ID
----------
         1

ops$tkyte%ORA11GR2> exec :txt := '1,2,3,4,5'

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> /

no rows selected

incorrect

Solomon Yakobson, December 08, 2014 - 7:23 pm UTC

Hi Tom. Look at:

where id in (select id from test where x in (select * from data) )

It selects any id that has x equal to either 1 or 2. As a result condition

having count(distinct x) = (select count(*) from data

will be true for all IDs that have x equal to either 1 or 2 and any second value. And if set we are looking for would be 1,2,3 it would return any ID that has 1,2,any or 1,3,any or 2,3,any or 1,any,any or 2,any,any or 3,any,any. For example:

SQL> select * from test;

        ID          X
---------- ----------
         1          1
         1          2
         1          3
         1          4
         2          1
         2          2
         2          3
         3          1
         3          2
         4          1
         4          6

11 rows selected.

SQL> with data
  2  as
  3  (
  4  select
  5    trim( substr (txt,
  6          instr (txt, ',', 1, level  ) + 1,
  7          instr (txt, ',', 1, level+1)
  8             - instr (txt, ',', 1, level) -1 ) )
  9      as token
 10     from (select ','||:txt||',' txt
 11             from dual)
 12   connect by level <=
 13      length(:txt)-length(replace(:txt,',',''))+1
 14   )
 15  select id
 16    from test
 17   where id in (select id from test where x in (select * from data) )
 18   group by id
 19  having count(distinct x) = (select count(*) from data)
 20  /

        ID
----------
         3
         4

SQL>

4 is returned simply because it has 2 distinct values, same as set we are looking for, and one of them is from the set we are looking for.

SY.

December 09, 2014 - 3:51 pm UTC

see above, we fixed that days ago... thanks...

i did something i normally do not do - i updated the original answer to reflect the correction

A reader, December 09, 2014 - 2:31 am UTC

Hi Tom,

you need 3 count one for overall for each id, second one for matched per id and third one for no of item in the list as below


SQL> select * from test;

        ID          X
---------- ----------
         1          1
         1          2
         1          3
         1          4
         2          1
         2          2
         2          3
         3          1
         3          2
         4          1
         4          6

        ID          X
---------- ----------
         3

12 rows selected.

SQL> exec :txt:='1'

PL/SQL procedure successfully completed.

SQL> ed
Wrote file afiedt.buf

  1  with data as
  2  (
  3  select level, length(txt)-length(replace(txt,',',''))-1 as itm_cnt,
  4         trim(substr (txt,
  5              instr (txt, ',', 1, level  ) + 1,
  6              instr (txt, ',', 1, level+1)
  7                 - instr (txt, ',', 1, level) -1 )) as tkn
  8        from (select ','||:txt||',' txt
  9                from dual)
 10      connect by level <
 11       length(txt)-length(replace(txt,',',''))
 12  )
 13  select distinct id
 14  from
 15  (
 16     select id, x, d.tkn, d.itm_cnt, tot_cnt, count(*)over(partition by t.id) as match_cnt
 17     from
 18     (select id, x, count(*)over(partition by id) as tot_cnt
 19     from test ) t Join data d
 20     on t.x=d.tkn
 21* )where itm_cnt=tot_cnt and tot_cnt=match_cnt
 22  /

no rows selected


SQL> exec :txt:='1,2'

PL/SQL procedure successfully completed.


SQL> /

no rows selected


SQL> exec :txt:='1,2,3'

PL/SQL procedure successfully completed.

SQL> /

        ID
----------
         2

SQL> exec :txt:='1,2,3,4'

PL/SQL procedure successfully completed.

SQL> /

        ID
----------
         1

SQL> exec :txt:='1,2,3,4,5'

PL/SQL procedure successfully completed.

SQL> /

no rows selected

SQL> delete from test where x is null
  2  ;

1 row deleted.

SQL> select * from test;

        ID          X
---------- ----------
         1          1
         1          2
         1          3
         1          4
         2          1
         2          2
         2          3
         3          1
         3          2
         4          1
         4          6

11 rows selected.

SQL> exec :txt:='1,2'

PL/SQL procedure successfully completed.


SQL> ed
Wrote file afiedt.buf

  1  with data as
  2  (
  3  select level, length(txt)-length(replace(txt,',',''))-1 as itm_cnt,
  4         trim(substr (txt,
  5              instr (txt, ',', 1, level  ) + 1,
  6              instr (txt, ',', 1, level+1)
  7                 - instr (txt, ',', 1, level) -1 )) as tkn
  8        from (select ','||:txt||',' txt
  9                from dual)
 10      connect by level <
 11       length(txt)-length(replace(txt,',',''))
 12  )
 13  select distinct id
 14  from
 15  (
 16     select id, x, d.tkn, d.itm_cnt, tot_cnt, count(*)over(partition by t.id) as match_cnt
 17     from
 18     (select id, x, count(*)over(partition by id) as tot_cnt
 19     from test ) t Join data d
 20     on t.x=d.tkn
 21* )where itm_cnt=tot_cnt and tot_cnt=match_cnt
 22  /

        ID
----------
         3

SQL>

December 09, 2014 - 3:54 pm UTC

see above, we fixed that days ago... thanks...

i did something i normally do not do - i updated the original answer to reflect the correction

An alternative to the 'with data' bit

Ian Roberts, March 17, 2015 - 2:50 pm UTC

An alternative to the connect by query in the 'with data' bit would be to pass the sting to XMLTable, which should be able to cope with any XQuery style sequence string.

SQL> variable txt varchar2(20)
SQL> exec :txt := '1,2'

PL/SQL procedure successfully completed.

SQL> SELECT x
  2    FROM XMLTABLE(:txt COLUMNS x INT PATH '.');

         X
----------
         1
         2

SQL> exec :txt := '1,2, 4, 6 to 9'

PL/SQL procedure successfully completed.

SQL> SELECT x
  2    FROM XMLTABLE(:txt COLUMNS x INT PATH '.');

         X
----------
         1
         2
         4
         6
         7
         8
         9

7 rows selected.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Another one

simpler

Collection based solution

incorrect

An alternative to the 'with data' bit

More to Explore

PL/SQL demos

PL/SQL docs

How to write generic queries

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Another one

simpler

Collection based solution

incorrect

An alternative to the 'with data' bit

More to Explore

PL/SQL demos

PL/SQL docs