Re: any syntax similar as " with select " clause in ibm db2?

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_at_yorku.ca> wrote in message
news:1673c1fd.0302281756.6c6da7e3_at_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

>