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
3  17466 
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> @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
I gave the following permission: grant create materialized view to hr;
and I still get the following error:ORA-12054: cannot set the ON
COMMIT refresh attribute for the materialized view.
Here is what I am doing: ( Release 9.2.0.4.0 )
CREATE MATERIALIZED VIEW mv_test BUILD IMMEDIATE REFRESH COMPLETE
ON COMMIT AS
SELECT COUNT(*), b.dept_id, b.dept_name, SUM(sal) salary,COUNT(sal)
FROM EMP a, DEPT b
WHERE a.dept_id = b.dept_id (+)
GROUP BY b.dept_id, b.dept_name
Frank van Bortel <fv********@netscape.net> wrote in message news:<c8**********@news3.tilbu1.nb.home.nl>... 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> @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
Maybe will help, if you create on base tables "MATERIALIZED VIEW LOG"
regards, Matej
"M. Mehta" <ml****@yahoo.com> wrote in message
news:a7**************************@posting.google.c om... I gave the following permission: grant create materialized view to hr;
and I still get the following error:ORA-12054: cannot set the ON
COMMIT refresh attribute for the materialized view.
Here is what I am doing: ( Release 9.2.0.4.0 )
CREATE MATERIALIZED VIEW mv_test BUILD IMMEDIATE REFRESH COMPLETE
ON COMMIT AS
SELECT COUNT(*), b.dept_id, b.dept_name, SUM(sal) salary,COUNT(sal)
FROM EMP a, DEPT b
WHERE a.dept_id = b.dept_id (+)
GROUP BY b.dept_id, b.dept_name
Frank van Bortel <fv********@netscape.net> wrote in message
news:<c8**********@news3.tilbu1.nb.home.nl>... 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> @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
This discussion thread is closed Replies have been disabled for this discussion. Similar topics
6 posts
views
Thread by Vincent LIDOU |
last post: by
|
6 posts
views
Thread by Bruce |
last post: by
|
1 post
views
Thread by Bruno BAGUETTE |
last post: by
|
2 posts
views
Thread by Shirley |
last post: by
| | | | |
1 post
views
Thread by Pink Panther |
last post: by
|
3 posts
views
Thread by M. Mehta |
last post: by
| | | | | | | | | | | | | | | | | | | | | |
