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
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
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
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
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
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
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
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
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
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.,
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
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
-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
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
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
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
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
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
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
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
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
21 matches
Mail list logo
