Hello All,
I am working on a Lab, prior to do it on Dev. I took an HR schema, and build a couple of tables,
CREATE TABLE foo(foo NUMBER, CONSTRAINT foo_pk PRIMARY KEY(foo));
CREATE TABLE bar(foo NUMBER, bar NUMBER, CONSTRAINT bar_pk PRIMARY KEY(foo, bar));
CREATE MATERIALIZED VIEW LOG ON foo WITH ROWID;
Create Materialized View Log On Bar With Rowid;
CREATE MATERIALIZED VIEW foo_bar
NOLOGGING
CACHE
BUILD IMMEDIATE
REFRESH FAST ON COMMIT AS SELECT foo.foo,
bar.bar,
foo.ROWID AS foo_rowid,
bar.ROWID AS bar_rowid
From Foo, Bar
WHERE foo.foo = bar.foo;
This was token from: sql - Oracle - How to create a materialized view with FAST REFRESH and JOINS - Stack Overflow
It works, but then I tried this:
I imported Peoplesoft's Location Table in HR Schema and replicated above: CREATE MATERIALIZED VIEW LOG ON Ps_Location_Tbl WITH ROWID;
What I need is to place on MV the lastest date of a given Location, like this:
CREATE MATERIALIZED VIEW SAT_LOCATION_MV
NOLOGGING
CACHE
BUILD IMMEDIATE
Refresh Fast On Commit As
Select
L.ROWID AS LOC_ROWID,
L.*
FROM HR.Ps_Location_Tbl L
WHERE L.Effdt = (SELECT MAX(L_Ed.Effdt) FROM Hr.Ps_Location_Tbl L_Ed
WHERE L.Setid = L_Ed.Setid
AND L.Location = L_Ed.Location
AND L.Effdt <= L_Ed.Effdt
);
If I ran the select, works very well, but when I copy and paste it on the MV definition...
I got this error:
Error SQL: ORA-12054: "cannot set the ON COMMIT refresh attribute for the materialized view"
*Cause: The materialized view did not satisfy conditions for refresh at
commit time.
*Action: Specify only valid options.
This is, perhaps because I have a subquery to retrieve MAX(EFFDT);
Question: How could I change the approach to build a MV?
Thanks in advance.
Hector