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 17933
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 thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Vincent LIDOU |
last post by:
Do not trust values returned by materialized views under SQL Server
without frequently checking underlying tables!!!
I already posted this message under microsoft.public.sqlserver.server
and I'm...
|
by: Bruce |
last post by:
I want to create a new table based on an existing table, but I don't
want the tables to have any enforced relationship. Is this possible
without having to do a CREATE TABLE and an INSERT?
...
|
by: Bruno BAGUETTE |
last post by:
Hello,
I'm trying to build a PL/PGSQL function that will be called by a trigger
which will update a table named 'mview_contacts'. That table plays the
role of a materialized view.
The...
|
by: Shirley |
last post by:
We are running DB2 on iSeries V5R2.
Using AQUA DATA STUDIO with a connection to our iSeries, I created a
view using SQL and I am trying to create an index on this view using
the code below.
...
|
by: holdingbe |
last post by:
Hi,
I created a materialized view. when i refresh that views, it shows error like
this materialized view not present...
I created materialized logs also.....
Regards...
|
by: rumasinha |
last post by:
Hi,
I first created a materialized view that was based on the org striped views like po_headers hence the automatic refresh was not happening.
After automatic complete refresh, the materialized...
|
by: Pink Panther |
last post by:
Hi All,
Sorry this may be a repost as first attempt died on me...
I have a set of 50 materialized views that are populated every morning.
However 2 of the materialized views contain no...
|
by: M. Mehta |
last post by:
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:
|
by: kewldotnet |
last post by:
I have a query thats taking long time to execute. So i have created a Materialized view to refresh it every hour. But when the Materialized view is being refreshed, there is no data in the...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |