Re: Can NOT create Materialized View

M. Mehta wrote:
> It seems that you can not create a materialized view if you are using
> outer joins...can someone please verify this?
>
> Thanks
> M. 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

Versions may help... this is 9.2.0.4:
SQL> connect system/manager
Connected.
SQL> grant create materialized view to hr;

Grant succeeded.

SQL> connect hr/hr
Connected.
SQL> _at_t

Materialized view created.

SQL> l

   1 create materialized view mv_test build immediate refresh complete    2 on commit as
   3 select count(*), b.department_id, b.department_name, sum(salary) salary,cou
nt(salary)

   4 from employees a, departments b
   5 where a.department_id = b.department_id (+)    6* group by b.department_id, b.department_name

-- 

Regards,
Frank van Bortel