Re: For Those Who Know DB2 and Oracle, Can You Take A Look

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Fri, 18 Oct 2002 09:08:29 +0100
Message-ID: <3dafc17e$0$1289$ed9e5944@reading.news.pipex.net>

"dudes" <NOSPAM_at_thankyou.com> wrote in message news:3DAF4DAD.6090203_at_thankyou.com...
> Does Oracle have anything similar to the following DB2 V6 query:
>
>
> With RangeTable(CRank, CLow, CHigh) as
> (
> values (00, 0, 999)
> union all
> select CRank+1, CLow+1000, CHigh+1000 from RangeTable where CRank < 50
> )
>
>
> Apparently, this creates a 50 row result set which can then be used to
> join to a real table. A guy in a DB2 newsgroup posted this in response
> to a question I had. I didn't know DB2 had something this powerful.

What I understand from your question is that you wish to use an arbitrarily large set of increasing numbers as a table to join to an actual table. I'd use

(select rownum from all_objects where rownum <51) in my from clause for this purpose.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer

******************************************

Received on Fri Oct 18 2002 - 03:08:29 CDT