insert into Z
WHERE A.col1=B.col1 and
And then, I'm updating a column COL3 in Z using another column in XYZ.
update Z SET COL3=
(select sum(amt) from XYZ WHERE
I need to write an mview for this and this is what I wrote:
CREATE MATERIALIZED VIEW mview_Z
REFRESH COMPLETE ON DEMAND
ENABLE QUERY REWRITE
A.col2=B.col2 AND Z.COL1=XYZ.COL1 AND
When I refresh the mview, data is not populated in the mview.
Am I doing it the right way?? I didnt know how to include the conditions in the update clause above. I initially used a sub query but it is not allowed in mviews.
Can I use the same table in the from clause for which I'm writing an Mview(in my case its the Z table)
Where are you specifying the XYZ alias in your from clause of the select. An mview is noting more then the stored results of a select. If the select is invalid, the mview will be invalid. Give some more information on the table structures involved. Also you said that a sub select in the select portion of the select is not allowed might be true, but the select can normally be rewritten to remove the need. What is your original MVIEW select.
You do not need a parachute to skydive. You only need a parachute to skydive twice.