Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Don.

Asked: October 19, 2016 - 11:23 pm UTC

Last updated: October 21, 2016 - 2:11 am UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

Can I create a view that uses a CTE?

and Connor said...

Yes. You just ... do it :-)

SQL> create or replace view V as
  2  with blah as ( select * from dba_source)
  3  select owner, count(*) c
  4  from blah
  5  group by owner;

View created.

SQL> desc V
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- --------------------
 OWNER                                                                            VARCHAR2(128)
 C                                                                                NUMBER



Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Don Simpson, October 20, 2016 - 9:26 pm UTC

Ah. I new I had some kind of syntax issue. I was trying to do it with parens in the wrong place:

create or replace view V 
as (
    with blah as ( select * from dba_source)
    select owner, count(*) c
    from blah
    group by owner
    );


Thanx so much for your help.

Connor McDonald
October 21, 2016 - 2:11 am UTC

glad we could help

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library