From: "Chris Leonard" <s_p_a_m_chris@hotmail.com>
Newsgroups: comp.databases.oracle
References: <1673c1fd.0302281756.6c6da7e3@posting.google.com>
Subject: Re: any syntax similar as " with select " clause in ibm db2?
Lines: 61
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
Message-ID: <4VK8a.7$G_.18385@news.uswest.net>
Date: Mon, 3 Mar 2003 09:54:38 -0600
NNTP-Posting-Host: 128.255.4.141
X-Trace: news.uswest.net 1046706880 128.255.4.141 (Mon, 03 Mar 2003 09:54:40 CST)
NNTP-Posting-Date: Mon, 03 Mar 2003 09:54:40 CST


Hello John,

I'm not sure that I understand your DB2-speak entirely, but Oracle's
implementation of inline views might interest you.  Basically, an inline
view is a query that appears in the FROM clause.  Here is a trivial example
(using the SCOTT schema):

    SELECT * FROM (SELECT * FROM DEPT);

Here is a only-slightly-less-trivial example:

    SELECT EMP.EMPNO, EMP.DEPTNO, DCOUNTS.TOTAL_EMPS
    FROM EMP
    JOIN (SELECT DEPTNO, COUNT(*) AS TOTAL_EMPS
    FROM EMP GROUP BY DEPTNO) DCOUNTS
    ON EMP.DEPTNO = DCOUNTS.DEPTNO;

That's not very similar to the DB2 syntax, but it does allow you to create
views on-the-fly in your queries which have different grouping levels, which
seemed to be what you were interested in.

--
Cheers,
Chris

___________________________________

Chris Leonard, The Database Guy
http://www.databaseguy.com

Brainbench MVP for Oracle Admin
http://www.brainbench.com

MCSE, MCDBA, OCP, CIW
___________________________________

"John" <johnwu@yorku.ca> wrote in message
news:1673c1fd.0302281756.6c6da7e3@posting.google.com...
> for ibm db2 sql select clause, "with" is powerful clause, as
>
> with viewtable_1 (v1,total) as
>   (select column_1,sum(...)
>       from table
>       group by column_1),
>      viewtable_2 (v2) as
>     (select total from viewtable_2)
> select * from viewtable_2;
>
> in above case, with..as used to like create a table, but these
> "viewtable" can be shared in the hierarchy of from the first generated
> to the last generated.
>
> Is there similar syntax/clause as illustrated above in oracle?
>
> --
> Thanks
> John Wu
> Toronto
>