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 17993
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_na me, 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_na me
--
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(sa l)
FROM EMP a, DEPT b
WHERE a.dept_id = b.dept_id (+)
GROUP BY b.dept_id, b.dept_name
Frank van Bortel <fv********@net scape.net> wrote in message news:<c8******* ***@news3.tilbu 1.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_na me, 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_na me
Maybe will help, if you create on base tables "MATERIALIZ ED VIEW LOG"
regards, Matej
"M. Mehta" <ml****@yahoo.c om> wrote in message
news:a7******** *************** ***@posting.goo gle.com... 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(sa l) FROM EMP a, DEPT b WHERE a.dept_id = b.dept_id (+) GROUP BY b.dept_id, b.dept_name
Frank van Bortel <fv********@net scape.net> wrote in message
news:<c8******* ***@news3.tilbu 1.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_na me, 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_na me 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 amazed nobody from Microsoft answered about this problem. By
inserting lots of data into our two main tables for about 30 minutes,
we can fail our materialized view that performs a count_big on those
two tables.
Executing (after of course...
|
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?
create table customer_Temp
as (select credit_Card_number, personal_id_number from customer)
DATA INITIALLY DEFERRED REFRESH deferred;
refresh table customer_temp;
|
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 'plpgsql' language is installed on that database (there are already
several functions inside) but I don't understand why I can't store that
function in the database.
|
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.
CREATE INDEX reports.Ivendorname ON reports.transbyvendor05
(vendorname)
However I get the following error:
|
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
Michael
| |
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 view was showing null records.
Hence I based the materialized view on "_all" tables and then based a view on this materialized view and this view I made org striped.
In the materialized view , I was refering to...
|
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 records (I know the master
does contain records).
None of the jobs are broken and all run as scheduled.
|
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 Materialized view table. I think it truncates the table before every refresh.
Is there any way to keep the old data while its being refreshed.?
|
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, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
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, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |