Why not do it like this...
select usr from gab
where val=1
intersect
select usr from gab
where val=5
intersect
select usr from gab
where val=7;
-----Original Message-----
Sent: Thursday, November 13, 2003 7:35 PM
To: Multiple recipients of list ORACLE-L
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_P
8_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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Bellow, Bambi
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
