Combining views and CTE

  • From: "Mladen Gogala" <dmarc-noreply@xxxxxxxxxxxxx> (Redacted sender "mgogala@xxxxxxxxx" for DMARC)
  • To: Oracle-L <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 13 Mar 2015 18:44:18 -0400

//
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

Other related posts: