Materialized View: ORA-22818 subquery expressions not allowed here

CREATE MATERIALIZED VIEW mv1 AS
(SELECT s.id AS sport_no
, c.rollno id
, (SELECT NAME FROM class c WHERE id = sport_no ) AS stu_name
from sport s);

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 mv1 as
select s.id AS sport_no
, c.rollno id
from sport s , class c
where s.id = c.rollno(+);

A simpler solution: creating MV on top of the view

CREATE or REPLACE VIEW view_sport_stu_name AS
(SELECT s.id AS sport_no
, c.rollno id
, (SELECT NAME FROM class c WHERE id = sport_no ) AS stu_name
from sport s);

create MATERIALIZED VIEW mview_sport_stu_name AS
select * from view_sport_stu_name;