No Subqueries Allowed in Materialized View? No Problem!

Have you ever run into the following error when trying to create a materialized view?

ORA-22818: subquery expressions not allowed here

Yes, it is true: You cannot have a scalar subquery in the SQL statement used to create your materialized view. Here's an example of what won't work (note: I am not claiming this query makes any sense):

CREATE MATERIALIZED VIEW hr_demo_mv
AS
 SELECT employee_id,
 (SELECT MAX (hire_date)
 FROM employees ce) maxhd
 FROM employees t
/

ORA-22818: subquery expressions not allowed here
22818. 00000 - "subquery expressions not allowed here"
*Cause: An attempt was made to use a subquery expression where these
 are not supported.
*Action: Rewrite the statement without the subquery expression.

Rewrite my query without the subquery expression? But I just spent an hour putting it all together. It works great. It gives me exactly the results I want and need. Rewrite it? ARGH.

Calm yourself. While it is true that you will need to "rewrite the statement" that you provide in your CREATE MATERIALIZED VIEW statement, you will not have to abandon your subqueries and all your hard work.

All you have to do is create a view with the subqueries, and then create your materialized view based on the view:

CREATE VIEW hr_demo_v
AS
 SELECT employee_id,
 (SELECT MAX (hire_date)
 FROM employees ce) maxhd
 FROM employees t
/

View HR_DEMO_V created.

CREATE MATERIALIZED VIEW hr_demo_mv
AS
 SELECT * FROM hr_demo_v
/

Materialized view HR_DEMO_MV created.

I recommend this approach (the materialized view is "nothing more" than a select from a view), even if your materialized view query does not contain a subquery or anything else that would preclude the materialized view from being created.

By taking this approach, you can change the contents of the materialized view with the next refresh by doing nothing more than changing the query (instead of dropping and re-creating the materialized view).