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