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).
