Re: Silly SQL Question
Jacques, I checked your example, I think there are some issues here: 1. Original queries provided below do use merge join. 2. We could have missing indexes which can exist on real system. 3. Timings below is not a criteria -- after gathering statistics and creation an index on val this both queries take about 1,3 seconds. So it means on your system you checked the *speed of sort operation* only -- because, most probably, merge was used. Even w/o index but with hash join it works much more faster -- 11.87 vs 1.25 (figures are not precise). 4. It'a all for nothing -- life is cruel and real-life examples are much more complex :) If you do not mind I would not continue this discussion. Thank you. -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. Jacques Kilchoer wrote: Mr. Begun, I didn't answer your comment about making the query work with the decode vs. using a user-written PL/SQL function because I wanted to compare timings for both. I created a table with 200,000 rows and compared the VB (Vladimir Begun) query to the JRK (my) query. The runtimes were identical (using SET TIMING ON in SQL*Plus). I agree that PL/SQL isn't necessary but I think it's easier to read, and the performance is the same. So there! In any case Ms. Bellows had a correct solution, which I mistakenly thought was false, so all this was just an intellectual exercise. With a 4-element list execute :list := '3,4,5,6,' SET TIMING ON results: VB query 11.87 JRK query: 11.67 (each query was run twice) If you don't hardcode the number of elements in the list in the 'AND cnt = ' clause, the times were slightly longer but still the same: 14.71 vs. 14.32 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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).
RE: Silly SQL Question
Jacques --
Huh? I thought the question was, give me all the usrs where there exists a
record containing a 1,5, and 7. How can that return one record, when there
are two users -- and only two users -- who have the 1,5,7 combination? The
data provided shows that both GAP and GPA have a 1, 5 and 7 and that no
other users do. My query provides that answer. If that wasn't the
question, then that won't be the answer; but if it *was* the question, then
the query is correct.
HTH,
Bambi.
=
create table gab (usr char(3),val number);
insert into gab values ('GAP',1);
insert into gab values ('GAP',5);
insert into gab values ('GAP',5);
insert into gab values ('GAP',7);
insert into gab values ('PAG',1);
insert into gab values ('PAG',7);
insert into gab values ('PAG',2);
insert into gab values ('JKL',1);
insert into gab values ('JKL',5);
insert into gab values ('JKL',5);
insert into gab values ('GPA',1);
insert into gab values ('GPA',5);
insert into gab values ('GPA',7);
insert into gab values ('GPA',8);
select usr from gab
where val=1
intersect
select usr from gab
where val=5
intersect
select usr from gab
where val=7;
USR
---
GAP
GPA
-Original Message-
Sent: Friday, November 14, 2003 7:24 PM
To: Multiple recipients of list ORACLE-L
-Original Message-
Bellow, Bambi
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;
Because that way you would get the wrong answer. With the sample data as
kindly provided by Mr. Begun the correct query would return one row, but
your query returns two rows.
SQL select * from gab ;
USR VAL
-- -
GAP1
GAP5
GAP5
GAP7
PAG1
PAG7
PAG2
JKL1
JKL5
JKL5
GPA1
GPA5
GPA7
GPA8
14 ligne(s) sélectionnée(s).
SQL select usr from gab
2 where val=1
3 intersect
4 select usr from gab
5 where val=5
6 intersect
7 select usr from gab
8 where val=7;
USR
--
GAP
GPA
--
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).
RE: Silly SQL Question
The original question was show me the users who have ALL the values in the list but NOT MORE than the values in the list. -Original Message- Bellow, Bambi Huh? I thought the question was, give me all the usrs where there exists a record containing a 1,5, and 7. How can that return one record, when there are two users -- and only two users -- who have the 1,5,7 combination? The data provided shows that both GAP and GPA have a 1, 5 and 7 and that no other users do. My query provides that answer. If that wasn't the question, then that won't be the answer; but if it *was* the question, then the query is correct. -- 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).
RE: Silly SQL Question
Just a guess: select distinct usr from xxx where (select count(*) from xxx group by Usr) = (select count(*) from xxx group by Usr, val) -Original Message- Sent: Thursday, November 13, 2003 3:29 PM To: Multiple recipients of list ORACLE-L 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. TIA Gabriel select usr from XXX where val in (list) group by usr having count(*) = number of values in list does it but assumes that (usr, val) is unique (which can be easily worked-around : select usr from (select distinct usr, val from XXX) group by ... ) and also that you know both the list and the number of items in the list, which looks reasonable. If your intent is to build the queries and the list dynamically, I'd rather suggest storing the list into a temporary table. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult 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). This e-mail, including attachments, may include confidential and/or proprietary information, and may be used only by the person or entity to which it is addressed. If the reader of this e-mail is not the intended recipient or his or her authorized agent, the reader is hereby notified that any dissemination, distribution or copying of this e-mail is prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and delete this e-mail immediately. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Michael Milligan 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).
Re: Silly SQL Question
Using Bambi's table and values. Try this query, it's ugly but it works (kind of like the contributor...) Daniel 1 select o.usr, count(o.usr) 2 from (select distinct usr, val 3from gab 4where val in (1,5,7) 5 and usr not in (select usr 6 from gab 7 where val not in (1,5,7))) o 8 group by o.usr 9* having count(o.usr) = 3 Jacques Kilchoer wrote: The original question was show me the users who have ALL the values in the list but NOT MORE than the values in the list. -Original Message- Bellow, Bambi Huh? I thought the question was, give me all the usrs where there exists a record containing a 1,5, and 7. How can that return one record, when there are two users -- and only two users -- who have the 1,5,7 combination? The data provided shows that both GAP and GPA have a 1, 5 and 7 and that no other users do. My query provides that answer. If that wasn't the question, then that won't be the answer; but if it *was* the question, then the query is correct. -- 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: Daniel Fink 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).
RE: Silly SQL Question
Gabriel, How about this untested code? Alan. select usr from ( select usr, sum(decode(val,1,1,0)) look1, -- flag for 1 sum(decode(val,5,5,0)) look2, -- flag for 5 sum(decode(val,7,7,0)) look3, -- flag for 7 sum(decode(val,1,0,5,0,7,0,1)) look4 -- flag for others from the_table group by usr ) where look1 = 1 AND look2 = 1 AND look3 = 1 AND look4 = 0 / -Original Message- Sent: Thursday, November 13, 2003 2:05 PM To: Multiple recipients of list ORACLE-L 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. TIA Gabriel __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gabriel Aragon 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: Alan Gano 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).
RE: Silly SQL Question
Ok, guys I have to apologize twice,
First: the delay to answer your very kind emails, (you
know the urgent problems dont let you time for the
important ones)
Second: maybe my question was not clear enough,
ciertanly what Bambi says is what I need give me all
the usrs where there exists a record containing 1 AND
5 AND 7, the criteria was the list, not the records,
so it does not matter if the user has many more
records, but if he/she has those records that are in
the list, that is what I want, the solution is as
simple as Bambi's query.
I really really appreciate all the solutions provided
for you guys, I swear I tested every one.
Below I have a copy of my original email.
Thank you very much!
Gabriel Aragon
+++
I have a table 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.
Gabriel
+++
--- Bellow, Bambi [EMAIL PROTECTED] wrote:
Jacques --
Huh? I thought the question was, give me all the
usrs where there exists a
record containing a 1,5, and 7. How can that
return one record, when there
are two users -- and only two users -- who have the
1,5,7 combination? The
data provided shows that both GAP and GPA have a 1,
5 and 7 and that no
other users do. My query provides that answer. If
that wasn't the
question, then that won't be the answer; but if it
*was* the question, then
the query is correct.
HTH,
Bambi.
=
create table gab (usr char(3),val number);
insert into gab values ('GAP',1);
insert into gab values ('GAP',5);
insert into gab values ('GAP',5);
insert into gab values ('GAP',7);
insert into gab values ('PAG',1);
insert into gab values ('PAG',7);
insert into gab values ('PAG',2);
insert into gab values ('JKL',1);
insert into gab values ('JKL',5);
insert into gab values ('JKL',5);
insert into gab values ('GPA',1);
insert into gab values ('GPA',5);
insert into gab values ('GPA',7);
insert into gab values ('GPA',8);
select usr from gab
where val=1
intersect
select usr from gab
where val=5
intersect
select usr from gab
where val=7;
USR
---
GAP
GPA
-Original Message-
Sent: Friday, November 14, 2003 7:24 PM
To: Multiple recipients of list ORACLE-L
-Original Message-
Bellow, Bambi
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;
Because that way you would get the wrong answer.
With the sample data as
kindly provided by Mr. Begun the correct query would
return one row, but
your query returns two rows.
SQL select * from gab ;
USR VAL
-- -
GAP1
GAP5
GAP5
GAP7
PAG1
PAG7
PAG2
JKL1
JKL5
JKL5
GPA1
GPA5
GPA7
GPA8
14 ligne(s) sélectionnée(s).
SQL select usr from gab
2 where val=1
3 intersect
4 select usr from gab
5 where val=5
6 intersect
7 select usr from gab
8 where val=7;
USR
--
GAP
GPA
--
=
Any dream worth having is a dream worth fighting for(Cualquier sueño que valga la
pena tener, es un sueño por el que vale la pena luchar)Charles Xavier
__
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Gabriel Aragon
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).
Re: Silly SQL Question
Jacques Kilchoer wrote: Mr. Begun: I'm not convinced that your answer is quite the right one. I've provided two solutions but I'm still confused :). Jacques, does that mean that I understand English and the original query was Ok? :) -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. Gabriel Aragon wrote: Ok, guys I have to apologize twice, ... -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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).
RE: Silly SQL Question
Mr. Begun,
I didn't answer your comment about making the query work with the decode vs. using a
user-written PL/SQL function because I wanted to compare timings for both. I created a
table with 200,000 rows and compared the VB (Vladimir Begun) query to the JRK (my)
query. The runtimes were identical (using SET TIMING ON in SQL*Plus).
I agree that PL/SQL isn't necessary but I think it's easier to read, and the
performance is the same. So there!
In any case Ms. Bellows had a correct solution, which I mistakenly thought was false,
so all this was just an intellectual exercise.
With a 4-element list
execute :list := '3,4,5,6,'
SET TIMING ON results: VB query 11.87 JRK query: 11.67 (each query was run twice)
If you don't hardcode the number of elements in the list in the 'AND cnt = ' clause,
the times were slightly longer but still the same: 14.71 vs. 14.32
VB query:
SELECT usr
FROM (
SELECT DISTINCT usr, val, COUNT(DISTINCT val) OVER (PARTITION BY usr) cnt
FROM gab
)
WHERE val IN (SELECT DISTINCT element FROM (
SELECT TO_NUMBER(SUBSTR(:list, p, c - p)) element
FROM (
SELECT DECODE(ROWNUM - 1, 0, 1, INSTR(:list, ',', 1, ROWNUM - 1) + 1) p
, INSTR(:list, ',', 1, ROWNUM) c
FROM gab
WHERE ROWNUM = LENGTH(TRANSLATE(:list, ',0123456789-E', ','))
)
)
)
AND cnt = 4 -- it's for nothing, because count can be give by caller
GROUP BY
usr
, cnt
HAVING COUNT(*) = 4 ;
JRK query:
select a.usr
from
(select distinct
b.usr, b.val, count (distinct b.val) over (partition by b.usr) cnt
from gab b
) a
where
val in (select *
from
the (select
cast (str_to_tbl (:num_list) as my_number_table)
from dual
)
)
and cnt = 4
group by
usr, cnt
having
count(*) = cnt ;
Test data creation:
drop table gab;
create table gab
(usr varchar2(10) not null, val number not null) ;
declare
insert_cnt constant pls_integer := 20 ;
commit_cnt constant pls_integer := 2000 ;
i pls_integer ;
j pls_integer ;
k pls_integer ;
l pls_integer ;
n pls_integer ;
usr gab.usr%type ;
type usrt is table of gab.usr%type index by binary_integer ;
usra usrt ;
type valt is table of gab.val%type index by binary_integer ;
vala valt ;
begin
dbms_random.initialize (dbms_utility.get_time) ;
i := 1 ;
while i = insert_cnt
loop
usr := chr (ascii ('A') + mod (abs (dbms_random.random), 26))
|| chr (ascii ('A') + mod (abs (dbms_random.random), 26))
|| chr (ascii ('A') + mod (abs (dbms_random.random), 26)) ;
n := mod (abs (dbms_random.random), 5) + 1 ;
j := mod (i - 1, commit_cnt) + 1 ;
k := least (commit_cnt, j + n - 1) ;
for l in j..k
loop
usra (l) := usr ;
vala (l) := mod (abs (dbms_random.random), 9) + 1 ;
end loop ;
i := i + k - j + 1 ;
if k = commit_cnt or i = insert_cnt
then
forall m in 1..k
insert into gab (usr, val)
values (usra (m), vala (m)) ;
commit ;
end if ;
end loop ;
commit ;
end ;
/
--
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).
Re: Silly SQL Question
Jacques,
you can use my first name -- Mr. is too official for this list :). You have modified
the query,
however I would suggest you to check execution plan (and present it here) and remove
LENGTH(TRANSLATE(:list, ',0123456789-E', ',')) by replacing it using number of
elements in the
list i.e., in your case, 4. As I already said, it was just an example, in real life I
would think
is it Ok or not Ok to use it.
Timing is not everything you can check, consider statisticts. Did you consider
indexing val?
--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.
Jacques Kilchoer wrote:
Mr. Begun,
I didn't answer your comment about making the query work with the decode vs. using a
user-written PL/SQL function because I wanted to compare timings for both. I created a
table with 200,000 rows and compared the VB (Vladimir Begun) query to the JRK (my)
query. The runtimes were identical (using SET TIMING ON in SQL*Plus).
I agree that PL/SQL isn't necessary but I think it's easier to read, and the
performance is the same. So there!
In any case Ms. Bellows had a correct solution, which I mistakenly thought was false, so all this was just an intellectual exercise.
With a 4-element list
execute :list := '3,4,5,6,'
SET TIMING ON results: VB query 11.87 JRK query: 11.67 (each query was run twice)
If you don't hardcode the number of elements in the list in the 'AND cnt = ' clause, the times were slightly longer but still the same: 14.71 vs. 14.32
VB query:
SELECT usr
FROM (
SELECT DISTINCT usr, val, COUNT(DISTINCT val) OVER (PARTITION BY usr) cnt
FROM gab
)
WHERE val IN (SELECT DISTINCT element FROM (
SELECT TO_NUMBER(SUBSTR(:list, p, c - p)) element
FROM (
SELECT DECODE(ROWNUM - 1, 0, 1, INSTR(:list, ',', 1, ROWNUM - 1) + 1) p
, INSTR(:list, ',', 1, ROWNUM) c
FROM gab
WHERE ROWNUM = LENGTH(TRANSLATE(:list, ',0123456789-E', ','))
)
)
)
AND cnt = 4 -- it's for nothing, because count can be give by caller
GROUP BY
usr
, cnt
HAVING COUNT(*) = 4 ;
JRK query:
select a.usr
from
(select distinct
b.usr, b.val, count (distinct b.val) over (partition by b.usr) cnt
from gab b
) a
where
val in (select *
from
the (select
cast (str_to_tbl (:num_list) as my_number_table)
from dual
)
)
and cnt = 4
group by
usr, cnt
having
count(*) = cnt ;
Test data creation:
drop table gab;
create table gab
(usr varchar2(10) not null, val number not null) ;
declare
insert_cnt constant pls_integer := 20 ;
commit_cnt constant pls_integer := 2000 ;
i pls_integer ;
j pls_integer ;
k pls_integer ;
l pls_integer ;
n pls_integer ;
usr gab.usr%type ;
type usrt is table of gab.usr%type index by binary_integer ;
usra usrt ;
type valt is table of gab.val%type index by binary_integer ;
vala valt ;
begin
dbms_random.initialize (dbms_utility.get_time) ;
i := 1 ;
while i = insert_cnt
loop
usr := chr (ascii ('A') + mod (abs (dbms_random.random), 26))
|| chr (ascii ('A') + mod (abs (dbms_random.random), 26))
|| chr (ascii ('A') + mod (abs (dbms_random.random), 26)) ;
n := mod (abs (dbms_random.random), 5) + 1 ;
j := mod (i - 1, commit_cnt) + 1 ;
k := least (commit_cnt, j + n - 1) ;
for l in j..k
loop
usra (l) := usr ;
vala (l) := mod (abs (dbms_random.random), 9) + 1 ;
end loop ;
i := i + k - j + 1 ;
if k = commit_cnt or i = insert_cnt
then
forall m in 1..k
insert into gab (usr, val)
values (usra (m), vala (m)) ;
commit ;
end if ;
end loop ;
commit ;
end ;
/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Vladimir Begun
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).
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
)
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 vals 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
-- -
GAP1
GAP5
GAP7
GAP9
JKL8
JKL5
XXX1
XXX5
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 *
8from
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_nnumber ;
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
RE: Silly SQL Question
Yes, your query was much better. I keep on forgetting about those analytic functions. Shame on me. I still think using a PL/SQL function to be able to easily change the IN list is worth the time and trouble. Plus it makes the explain plan is more interesting with the str_to_tbl function, you get to see the COLLECTION ITERATOR (PICKLER FETCH) -Original Message- Vladimir Begun ... PL/SQL is not needed to solve this task as SQL task. There reason when it would be wise to rewrite it is out of scope of this topic (but the reason is obvious). I'm just thinking that the query proposed by you is a bit expensive. So, I've re-scribbled mine: SELECT usr FROM ( SELECT DISTINCT usr, val, COUNT(DISTINCT val) OVER (PARTITION BY usr) cnt FROM gab ) WHERE val IN (1, 5, 7) AND cnt = 3 GROUP BY usr , cnt HAVING COUNT(*) = cnt / -- 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).
RE: Silly SQL Question
-Original Message- Bellow, Bambi 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; Because that way you would get the wrong answer. With the sample data as kindly provided by Mr. Begun the correct query would return one row, but your query returns two rows. SQL select * from gab ; USR VAL -- - GAP1 GAP5 GAP5 GAP7 PAG1 PAG7 PAG2 JKL1 JKL5 JKL5 GPA1 GPA5 GPA7 GPA8 14 ligne(s) sélectionnée(s). SQL select usr from gab 2 where val=1 3 intersect 4 select usr from gab 5 where val=5 6 intersect 7 select usr from gab 8 where val=7; USR -- GAP GPA -- 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).
Re: Silly SQL Question
Jacques Jacques Kilchoer wrote: I still think using a PL/SQL function to be able to easily change the IN list is worth the time and trouble. If the given list is created properly, which I think it's a must in this case, one would not need to use PL/SQL, the task can be solved in SQL only. Below is just *an example*, not a generic solution. VAR list VARCHAR2(30); -- number could be counted as well, not a big deal EXEC :list := '1,7,5,'; WITH numbers AS ( SELECT TO_NUMBER(SUBSTR(:list, p, c - p)) element FROM ( SELECT DECODE(ROWNUM - 1, 0, 1, INSTR(:list, ',', 1, ROWNUM - 1) + 1) p , INSTR(:list, ',', 1, ROWNUM) c FROM gab WHERE ROWNUM = LENGTH(TRANSLATE(:list, ',0123456789-E', ',')) ) ) SELECT usr FROM ( SELECT DISTINCT usr, val, COUNT(DISTINCT val) OVER (PARTITION BY usr) cnt FROM gab ) WHERE val IN (SELECT DISTINCT element FROM numbers) AND cnt = (SELECT COUNT(DISTINCT element) FROM numbers) -- it's for nothing, because count can be give by caller GROUP BY usr , cnt HAVING COUNT(*) = (SELECT COUNT(DISTINCT element) FROM numbers) -- same / Again, it's not a generic solution but it's Ok to use it for this particular task -- the number of elements is limited anyway. One could add yet one condition to avoid troubles with TO_NUMBER conversion, it's easy but I'm leaving it as is. Plus it makes the explain plan is more interesting with the str_to_tbl function, you get to see the COLLECTION ITERATOR (PICKLER FETCH) That's obviously nice :) but I think it's not a reason to use PL/SQL to solve this task. -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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).
Re: Silly SQL Question
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. TIA Gabriel select usr from XXX where val in (list) group by usr having count(*) = number of values in list does it but assumes that (usr, val) is unique (which can be easily worked-around : select usr from (select distinct usr, val from XXX) group by ... ) and also that you know both the list and the number of items in the list, which looks reasonable. If your intent is to build the queries and the list dynamically, I'd rather suggest storing the list into a temporary table. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult 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).
RE: Silly SQL Question
Here's one solution. SELECT usr FROM xxx GROUP BY usr HAVING SUM(DECODE(val,1,1,0)) 0 AND SUM(DECODE(val,5,1,0)) 0 AND SUM(DECODE(val,7,1,0)) 0 HTH Tony Aponte -Original Message- Sent: Thursday, November 13, 2003 5:05 PM To: Multiple recipients of list ORACLE-L 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. TIA Gabriel __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gabriel Aragon 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: Aponte, Tony 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).
RE: Silly SQL Question
Sorry I missed one. Try this. SELECT usr FROM bogus GROUP BY usr HAVING SUM(DECODE(val,1,1,0)) 0 AND SUM(DECODE(val,5,1,0)) 0 AND SUM(DECODE(val,7,1,0)) 0 AND SUM(DECODE(val,1,0,5,0,7,0,val)) = 0 Tony Aponte -Original Message- Sent: Thursday, November 13, 2003 5:30 PM To: Multiple recipients of list ORACLE-L Here's one solution. SELECT usr FROM xxx GROUP BY usr HAVING SUM(DECODE(val,1,1,0)) 0 AND SUM(DECODE(val,5,1,0)) 0 AND SUM(DECODE(val,7,1,0)) 0 HTH Tony Aponte -Original Message- Sent: Thursday, November 13, 2003 5:05 PM To: Multiple recipients of list ORACLE-L 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. TIA Gabriel __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gabriel Aragon 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: Aponte, Tony 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: Aponte, Tony 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).
Re: Silly SQL Question
Gabriel
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.
--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.
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.
TIA
Gabriel
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Vladimir Begun
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).
RE: Silly SQL Question
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 vals 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
-- -
GAP1
GAP5
GAP7
GAP9
JKL8
JKL5
XXX1
XXX5
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 *
8from
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_nnumber ;
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
Re: Silly SQL Question
Jacques
Yes, probably, you are right. I've overlooked example section,
given by Gabriel.
DROP TABLE gab;
CREATE TABLE gab (usr VARCHAR2(10) NOT NULL, val NUMBER NOT NULL);
INSERT INTO gab VALUES('GAP', 1);
INSERT INTO gab VALUES('GAP', 5);
INSERT INTO gab VALUES('GAP', 5);
INSERT INTO gab VALUES('GAP', 7);
INSERT INTO gab VALUES('PAG', 1);
INSERT INTO gab VALUES('PAG', 7);
INSERT INTO gab VALUES('PAG', 2);
INSERT INTO gab VALUES('JKL', 1);
INSERT INTO gab VALUES('JKL', 5);
INSERT INTO gab VALUES('JKL', 5);
INSERT INTO gab VALUES('GPA', 1);
INSERT INTO gab VALUES('GPA', 5);
INSERT INTO gab VALUES('GPA', 7);
INSERT INTO gab VALUES('GPA', 8);
COMMIT;
PL/SQL is not needed to solve this task as SQL task. There
reason when it would be wise to rewrite it is out of scope
of this topic (but the reason is obvious).
I'm just thinking that the query proposed by you is a bit
expensive. So, I've re-scribbled mine:
SELECT usr
FROM (
SELECT DISTINCT usr, val, COUNT(DISTINCT val) OVER (PARTITION BY usr) cnt
FROM gab
)
WHERE val IN (1, 5, 7)
AND cnt = 3
GROUP BY
usr
, cnt
HAVING COUNT(*) = cnt
/
HTH,
--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.
Jacques Kilchoer wrote:
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 vals in the table.
The HAVING COUNT (*) = should also match the number of distinct rows for usr.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Vladimir Begun
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).
