Re: PL/SQL help

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 28 Sep 2006 11:57:44 -0700
Message-ID: <1159469862.418058@bubbleator.drizzle.com>

Maxim Demenko wrote:

> Unfortunately, will lead to an error...
> ;-)
>
> Best regards
>
> Maxim

Au contraire.

CREATE TABLE t1 (
pname VARCHAR2(10),
cases NUMBER(3));

INSERT INTO t1 VALUES ('Smith',2);
INSERT INTO t1 VALUES ('John',3);
INSERT INTO t1 VALUES ('Mary',2);
INSERT INTO t1 VALUES ('Kate',1);

COMMIT; SELECT * FROM t1;

CREATE TABLE t2 AS
SELECT pname FROM t1
WHERE 1=2; SELECT * FROM t2;

BEGIN
   INSERT INTO t2
   WITH rn AS (

     SELECT rownum rn
     FROM dual
     CONNECT BY LEVEL <= (SELECT MAX(cases) FROM t1))

   SELECT pname
   FROM t1, rn
   WHERE rn <= cases
   ORDER BY pname;
END;
/

SELECT * FROM t2;

-- 
Daniel Morgan
University of Washington
Puget Sound Oracle Users Group

Received on Thu Sep 28 2006 - 13:57:44 CDT