Re: 'select <subquery>' not allowed in materialized view?

Your versions can't provide the same functionality as a subquery in the select clause can. As I pointed out in my original posting, that query was there only to illustrate the issue. There wasn't any point in trying to rewrite it. The question was, is this restriction (and possible others) documented somewhere?

Richard

Daniel Morgan wrote:
>
> Sybrand Bakker wrote:
>
> > On Thu, 29 Aug 2002 18:41:53 GMT, Richard Kuhler <noone_at_nowhere.com>
> > wrote:
> >
> > >I can create a table with this subquery but not a materialized view.
> > >The Oracle documentation for CREATE MATERIALIZED VIEW says "any valid
> > >SQL subquery". Apparently that's not true. Does somebody know where
> > >this (and other?) restrictions are documented?
> > >
> > >
> > >create table t_subq as
> > >select (select dummy from dual) as dummy
> > >from dual
> > >/
> > >
> > >Table created.
> > >
> > >
> > >create materialized view mv_subq as
> > >select (select dummy from dual) as dummy
> > >from dual
> > >/
> > >
> > >select (select dummy from dual) as dummy
> > > *
> > >ERROR at line 2:
> > >ORA-22818: subquery expressions not allowed here
> > >
> > >
> > >Obviously, this is just a demonstration of the problem not the actual
> > >query I want to use.
> > >
> > >
> > >Richard
> >
> > No version of course
> >
> > Sybrand Bakker, Senior Oracle DBA
> >
> > To reply remove -verwijderdit from my e-mail address
>
> It doesn't work in 8.1.7 but the following does rather nicely:
>
> create materialized view mv_subq as
> select dummy as dumy
> from (select dummy from dual);
>
> as does
>
> create materialized view mv_subq as
> select *
> from dual
> where dummy IN (
> select dummy
> from dual);
>
> For me, at least, the original syntax was too weird for words.
>
> Daniel Morgan
Received on Thu Aug 29 2002 - 17:54:52 CDT