Re: LOOPING SQL

"Dang" <mdang_at_bigpond.com.au> wrote in message news:<c0el3f$9n6$1_at_mws-stat-syd.cdn.telstra.com.au>...
> I am running Oracle 8i and trying to run a Loop of SQL based on one number.
> Problem is this number can vary.
>
> Example:
> How to convert this SQL into one Statement SQL based on LOOP
>
> SELECT SYSDATE, LOOP FROM
> (SELECT SYSDATE, '1' AS LOOP FROM dual
> UNION
> SELECT SYSDATE, '2' AS LOOP FROM dual
> UNION
> SELECT SYSDATE, '3' AS LOOP FROM dual
> UNION
> SELECT SYSDATE, '4' AS LOOP FROM dual
> UNION
> SELECT SYSDATE, '5' AS LOOP FROM dual
> )
>
> Result
>
> SYSDATE LOOP
> 12/02/2004 12:28:32 pm 1
> 12/02/2004 12:28:32 pm 2
> 12/02/2004 12:28:32 pm 3
> 12/02/2004 12:28:32 pm 4
> 12/02/2004 12:28:32 pm 5

What you are looking for is a function where you input one row and get many rows back. I'm not sure it is possible to "artificially" create rows in 8i. In 9i you can create a pipelined PL/SQL function to do this.

Dave Received on Thu Feb 12 2004 - 02:12:41 CST