Re: Combining views and CTE

From: Sayan Malakshinov <xt.and.r_at_gmail.com>
Date: Sat, 14 Mar 2015 01:51:40 +0300
Message-ID: <CAOVevU4umPeBSbRWLgkiunXBo_GDMzTzP7G+TKNtASeCPGGmhA_at_mail.gmail.com>

Just remove parentheses:

SQL> create or replace view v1 as
  2 (with q1 as (select * from emp e where e.first_name='John')   3 select * from q1)
  4 ;
 select * from q1)

                 *

ERROR at line 3:
ORA-32034: unsupported use of WITH clause

SQL> create or replace view v1 as
  2 with q1 as (select * from emp e where e.first_name='John')   3 select * from q1;

View created.

On Sat, Mar 14, 2015 at 1:44 AM, Mladen Gogala <dmarc-noreply_at_freelists.org> wrote:

> 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 DBAhttp://mgogala.freehostia.com
>
>

-- 
Best regards,
Sayan Malakshinov
Senior performance tuning engineer
PSBank
http://orasql.org

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

Received on Fri Mar 13 2015 - 23:51:40 CET