Re: PL/SQL help

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Thu, 28 Sep 2006 21:07:10 +0200
Message-ID: <efh6h1$og0$02$1@news.t-online.com>

DA Morgan schrieb:
> 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;

I never said, it can't be turned into PL SQL!!! ;-)

Best regards

Maxim Received on Thu Sep 28 2006 - 14:07:10 CDT