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

I would be very interested in seeing it, if there is such a site that can tell you all the restrictions associated with the queries used in a materialized view. Even deciding whether a materilaed view is simple or complex is no easy matter. Try looking up ORACLE DOC and see if you can memorize all the criteria needed for a materilaized view to be qualified as simple or complex. ORACLE does list some resrictions for "fast refresh" for MVs with subqueries, but they are not for the subquery itself.

http://docs.oracle.com/cd_a97630/server.920/a96567/repmview.htm#28373

Richard Kuhler <noone_at_nowhere.com> wrote in message news:<RVtb9.53278$_7.6401658_at_twister.socal.rr.com>...
> 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
Received on Thu Aug 29 2002 - 18:59:42 CDT