Can NOT create Materialized View

From: M. Mehta <mlal72_at_yahoo.com>
Date: 24 May 2004 11:50:58 -0700
Message-ID: <a7fd3ab8.0405241050.6bd847ce_at_posting.google.com>

It seems that you can not create a materialized view if you are using outer joins...Please help...I want to create the materialized view below with an outer join.

Thanks
Mehta  

Please follow my example below:  

created 2 tables:  

select * from emp;  

    EMP_ID DEPT_ID SAL
---------- ---------- ----------

         1          1      20000
         2          1      50000
 

select * from dept;  

   DEPT_ID DEPT_NAME
---------- ----------

         1 HR
         2 IT
         3 CC
 

create materialized view (with out outer join): SQL> create materialized view mv_test build immediate refresh complete on commit as
  2 select count(*), b.dept_id, b.dept_name, sum(sal) salary, count(sal)
  3 from emp a, dept b
  4 where a.dept_id = b.dept_id
  5 group by b.dept_id, b.dept_name
  6 /  

Materialized view created.  

SQL> drop materialized view mv_test;  

create materialized view (WITH outer join): SQL> create materialized view mv_test build immediate refresh complete on commit as
  2 select count(*), b.dept_id, b.dept_name, sum(sal) salary, count(sal)
  3 from emp a, dept b
  4 where a.dept_id = b.dept_id (+)
  5 group by b.dept_id, b.dept_name
  6 /
from emp a, dept b

     *
ERROR at line 3:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view Received on Mon May 24 2004 - 20:50:58 CEST