Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Silly SQL Question

RE: Silly SQL Question

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Thu, 13 Nov 2003 17:34:32 -0800
Message-ID: <F001.005D6999.20031113173432@fatcity.com> 





Mr. Begun: I'm not convinced that your answer is quite the right one.
I tried


INSERT INTO gab VALUES ('GAP', 9) ;


and then this query


SELECT usr


   FROM (


        SELECT DISTINCT usr, val FROM gab
        )




  WHERE val IN (1, 5, 7)


  GROUP BY



        usr


HAVING COUNT(*) = 3 -- number of elements in the list


/



returned the value 'GAP' even though 'GAP' has 4 "val"s in the table.
The HAVING COUNT (*) = should also match the number of distinct rows for usr.



Inspired by Tom Kyte's answer


"varying elements in IN list"


http://asktom.oracle.com/pls/ask/f?p=4950:8:8788433637134280281::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:110612348061,



I propose this solution, using a str_to_tbl function (see function definition after the proof of concept.)
SQL> select * from gab ;


USR              VAL



---------- ---------

GAP                1
GAP                5
GAP                7
GAP                9
JKL                8
JKL                5
XXX                1
XXX                5




8 ligne(s) sélectionnée(s).



SQL> variable num_list varchar2 (4000)


SQL> select b.usr


  2   from

  3     (select distinct a.usr, a.val from gab a) b,
  4     (select c.usr, count (*) as num_usr_val from gab c group by c.usr) d
  5   where
  6     b.usr = d.usr and
  7     b.val in (select *
  8                from
  9                  the (select
 10                          cast (str_to_tbl (:num_list) as my_number_table)
 11                       from dual
 12                      )
 13              )




 14   group by b.usr, d.num_usr_val


 15   having

 16     count(*) = d.num_usr_val
 17     and count (*) = (select count (*)
 18                       from
 19                         the (select
 20                                 cast (str_to_tbl (:num_list) as my_number_table)
 21                              from dual
 22                             )
 23                     )




 24  



SQL> execute :num_list := '1,5'


Procédure PL/SQL terminée avec succès.


SQL> /



USR





XXX


SQL> execute :num_list := ' 8 , 5 '


Procédure PL/SQL terminée avec succès.


SQL> /



USR





JKL


SQL> execute :num_list := '1,5,7'


Procédure PL/SQL terminée avec succès.


SQL> /



aucune ligne sélectionnée



SQL> execute :num_list := '1,5,7,8'


Procédure PL/SQL terminée avec succès.


SQL> /



aucune ligne sélectionnée



SQL> execute :num_list := '1,5,7,9'


Procédure PL/SQL terminée avec succès.


SQL> /



USR





GAP


SQL> execute :num_list := '1,5,7,8,9'


Procédure PL/SQL terminée avec succès.


SQL> /



aucune ligne sélectionnée 



script:


drop table gab;


create table gab

   (usr varchar2(10) not null, val number not null) ;
insert into gab (usr, val) values ('GAP', 1) ;
insert into gab (usr, val) values ('GAP', 5) ;
insert into gab (usr, val) values ('GAP', 7) ;
insert into gab (usr, val) values ('GAP', 9) ;
insert into gab (usr, val) values ('JKL', 8) ;
insert into gab (usr, val) values ('JKL', 5) ;
insert into gab (usr, val) values ('XXX', 1) ;
insert into gab (usr, val) values ('XXX', 5) ;


commit ;


create or replace type my_number_table as table of number ;


/



create or replace function str_to_tbl (p_str in varchar2)
 return my_number_table


as

   l_str  varchar2 (32760) default p_str || ',' ;
   l_n    number ;
   l_pos  pls_integer default 1 ;
   l_data my_number_table := my_number_table () ;


begin


   loop

      l_n := instr (l_str, ',', l_pos) ;
      exit when (nvl (l_n, 0) = 0) ;
      l_data.extend ;
      l_data (l_data.count) := ltrim (rtrim (substr (l_str, l_pos, l_n - l_pos))) ;
      l_pos := l_n + 1 ;




   end loop;


   return l_data ;


end;


/



variable num_list varchar2 (4000)


select b.usr


 from


   (select distinct a.usr, a.val from gab a) b,
   (select c.usr, count (*) as num_usr_val from gab c group by c.usr) d
 where


   b.usr = d.usr and


   b.val in (select *

              from
                the (select
                        cast (str_to_tbl (:num_list) as my_number_table)
                     from dual
                    )
            )




 group by b.usr, d.num_usr_val


 having


   count(*) = d.num_usr_val


   and count (*) = (select count (*)

                     from
                       the (select
                               cast (str_to_tbl (:num_list) as my_number_table)
                            from dual
                           )
                   )





execute :num_list := '1,5'


/



execute :num_list := ' 8 , 5 '


/



execute :num_list := '1,5,7'


/



execute :num_list := '1,5,7,8'


/



execute :num_list := '1,5,7,9'


/



execute :num_list := '1,5,7,8,9'


/



> -----Original Message-----
> Vladimir Begun
> 
> DROP TABLE gab;
> CREATE TABLE gab (usr VARCHAR2(10) NOT NULL, val NUMBER NOT 
> NULL --, CONSTRAINT gab$uq UNIQUE (usr, val)
> );
> INSERT INTO gab VALUES('GAP', 1);
> INSERT INTO gab VALUES('GAP', 5);
> INSERT INTO gab VALUES('GAP', 7);
> INSERT INTO gab VALUES('JKL', 8);
> INSERT INTO gab VALUES('JKL', 5);
> COMMIT;
> 
> SELECT usr
>    FROM (
>         SELECT DISTINCT usr, val FROM gab
>         )
>   WHERE val IN (1, 5, 7)
>   GROUP BY
>         usr
> HAVING COUNT(*) = 3 -- number of elements in the list
> /
> 
> Depending on the existence of the constraint, here gab$uq, you can
> either use inline view of run it against original table.
> 
> Gabriel Aragon wrote:
> > I have a table with like this:
> > 
> > Usr  val
> > ----------
> > GAP  1
> > GAP  5
> > GAP  7
> > JKL  8
> > JKL  5
> > 
> > I need a query that returns the user (GAP o JKL) that
> > has ALL the values in a list. Example: Having the
> > list: 1,5,7 the result will be GAP, but with the
> > values 1,5 or 1,5,7,8 there will be no result.
> > 
> > select distinct usr 
> > from xxx 
> > where val = All (1,3,5)
> > 
> > I was trying the ALL operator but it works with part
> > of the list, I need the user that has (exactly) all
> > the values in the list. Any idea?
> > 
> > Maybe it's a simple solution, but after several hours
> > I feel blocked.



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jacques Kilchoer
  INET: Jacques.Kilchoer_at_quest.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).


Received on Thu Nov 13 2003 - 19:34:32 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US