Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: 'select <subquery>' not allowed in materialized view?
Sorry about leaving off the version. I tested it in 8.1.7.4.0,
9.0.1.3.0 and 9.2.0.1.0. I suspect that it works this way in all
versions.
Richard
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
Received on Thu Aug 29 2002 - 17:22:37 CDT