Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: 'select <subquery>' not allowed in materialized view?
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 - 15:56:19 CDT