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