Combining views and CTE

From: Mladen Gogala <mgogala_at_yahoo.com>
Date: Fri, 13 Mar 2015 18:44:18 -0400
Message-ID: <55036842.8080505_at_yahoo.com>

//
I have a large view that I would like to slice and dice using the WITH clause. Unfortunately, things do not work as I'd like them to:

    1 with q1 as (select * from emp where deptno=10)

       2 create or replace view v1 as

       3* select * from q1

    SQL> /     create or replace view v1 as

    *

    ERROR at line 2:

    ORA-00928: missing SELECT keyword

    Elapsed: 00:00:00.01

    SQL> I also tried a different approach:

    SQL> create or replace view v1 as

       2 (with q1 as (select * from emp where deptno=10)

       3 select * from q1);

    select * from q1)

                     *

    ERROR at line 3:

    ORA-32034: unsupported use of WITH clause

This, of course, is just an example. Is there any way to use CTE for creating views? The version is 11.2.0.4, with the January 2015. PSU. I will try with 12c later tonight, but I am not too optimistic. Does anybody have a recipe here?
Regards,

-- 
Mladen Gogala
Oracle DBA
http://mgogala.freehostia.com


--
http://www.freelists.org/webpage/oracle-l

Received on Fri Mar 13 2015 - 23:44:18 CET