| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Silly SQL Question
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
)
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
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 )
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 )
SQL> execute :num_list := '1,5'
Procédure PL/SQL terminée avec succès.
SQL> /
USR
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
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 ;
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 ;
from
the (select
cast (str_to_tbl (:num_list) as my_number_table)
from dual
)
)
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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bellow, Bambi INET: bbellow_at_chi.navtech.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 Fri Nov 14 2003 - 10:59:39 CST
 |
 |