Re: Silly SQL Question

From: Vladimir Begun <Vladimir.Begun_at_oracle.com>
Date: Fri, 14 Nov 2003 18:24:25 -0800
Message-ID: <F001.005D6B42.20031114182425@fatcity.com>

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: Vladimir.Begun_at_oracle.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 - 20:24:25 CST