Results 1 to 2 of 2

Thread: mviews

  1. #1
    Join Date
    Jul 2005
    Posts
    276

    Unanswered: mviews

    I'm inserting data into a table Z as below:
    insert into Z
    (col1,col2,amt....)
    select
    A.col1,A.col2,B.amt....
    from A,B,C
    WHERE A.col1=B.col1 and
    A.col2=B.col2
    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
    Z.COL1=XYZ.COL1 AND
    Z.COL2=XYZ.COL2)
    I need to write an mview for this and this is what I wrote:

    CREATE MATERIALIZED VIEW mview_Z
    BUILD IMMEDIATE
    REFRESH COMPLETE ON DEMAND
    ENABLE QUERY REWRITE
    AS
    SELECT
    A.COL1,A.COL2,XYZ.AMT....
    FROM A,B,C,Z
    WHERE
    A.col1=B.col1 and
    A.col2=B.col2 AND
    Z.COL1=XYZ.COL1 AND
    Z.COL2=XYZ.COL2
    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)

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    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.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •