Materialized View: ORA-22818 subquery expressions not allowed here

CREATE MATERIALIZED VIEW mv_accu_trans AS
    (SELECT t.id AS transaction_number
          , t.account_id
          , (SELECT NAME FROM account_t WHERE id = account_id) AS account_name
       from transaction_t t);

Error: ORA-22818 subquery expressions not allowed here
.

This is a documented restriction of Materialized view.

Solution: rewrite the query replacing scalar subquery with outer join

create meterialized view mv_accu_trans as
select t.id, t.acccount_id, a.name
  from transaction_t t, account_t a
 where t.account_id = a.id(+);

A simpler solution: creating MV on top of the view


CREATE or REPLACE VIEW v_accu_trans AS
    (SELECT t.id AS transaction_number
          , t.account_id
          , (SELECT NAME FROM account_t WHERE id = account_id) AS account_name
       from transaction_t t);

create MATERIALIZED VIEW mv_accu_trans AS
select * from v_accu_trans;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: