Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Invalid Options in View

Invalid Options in View

From: Arthur <amerar_at_iwc.net>
Date: 2 May 2002 12:39:21 -0700
Message-ID: <8b622eae.0205021139.4e97ef1c@posting.google.com>


Hello,

Can I create a materialized view off of one table? I am trying to create a nested materialized view with FAST ON COMMIT. My original attempt at a materialized view, joins both a table (payment_history) AND a view (audit_trail_view) in its FROM clause.

It seems that I cannot use a view in a materialized view.

So, in order to get around that, I figured I could create a nested materialized view. I am trying to create a materialized view on the audit_trail, and then I think I can create another materialized view which joins the payment_history and the audit_trail materialized view.

These views are quite complex, but I'm getting nowhere fast. I get a ORA-12051 on the audit_trail when I try to create the materialized view. Any suggestions? The table layouts are below.......maybe someone has a suggestion?

Thanks in advance to anyone who lends a helping hand.

CREATE MATERIALIZED VIEW AUDIT_TRAIL_MV
       REFRESH FAST ON COMMIT
AS
SELECT payment_history_key, caps_code,

          sum(nvl(adj_amt_cash,0)) adj_amt_cash,
          sum(nvl(adj_amt_check,0)) adj_amt_check,
          sum(nvl(adj_amt_credit,0)) adj_amt_credit,
          sum(nvl(adj_int,0)) adj_int,
          sum(nvl(adj_pen,0)) adj_pen,
          sum(nvl(adj_fund_amt,0)) adj_fund_amt,
          sum(nvl(adj_letter_of_credit,0)) adj_letter_of_credit,
          sum(nvl(adj_electronic_fund_xfer,0))
adj_electronic_fund_xfer,
          sum(nvl(adj_credit_others,0)) adj_credit_others,
          sum(nvl(adj_food_stamps,0)) adj_food_stamps,
          sum(nvl(adj_advice_of_credit,0)) adj_advice_of_credit
   FROM audit_trail
   GROUP BY payment_history_key, caps_code;

ERROR at line 16:
ORA-12051: ON COMMIT attribute is incompatible with other options

CREATE MATERIALIZED VIEW payment_history_mv

      TABLESPACE data_payhis_mv PARALLEL
      BUILD IMMEDIATE
      REFRESH FAST ON COMMIT

AS
SELECT trandatetime, batchnum, trannum, ej1_seqnum, tranname_ind, misc13, amt_paid,
      DECODE(at.caps_code, ph.caps_code,
      DECODE(IS_A_DATE(misc13),0,ph.trandatetime,1,TO_DATE(misc13,'MM/DD/YYYY'),ph.trandatetime),
      NULL,DECODE(IS_A_DATE(misc13),0,ph.trandatetime,1,TO_DATE(misc13,'MM/DD/YYYY'),ph.trandatetime),
      ph.trandatetime) orig_trandate,

   DECODE(at.caps_code, ph.caps_code, ph.caps_code, NULL, ph.caps_code, at.caps_code) caps_code,

   DECODE(at.caps_code, ph.caps_code, NVL(amt_cash,0) + adj_amt_cash, NULL, amt_cash, adj_amt_cash) amt_cash,

   DECODE(at.caps_code, ph.caps_code, NVL(amt_check,0) + adj_amt_check, NULL, amt_check, adj_amt_check) amt_check,

   DECODE(at.caps_code, ph.caps_code, NVL(amt_credit,0) + adj_amt_credit, NULL, amt_credit, adj_amt_credit) amt_credit,

   DECODE(at.caps_code, ph.caps_code, NVL(advice_of_credit,0) + adj_advice_of_credit, NULL, advice_of_credit, adj_advice_of_credit) advice_of_credit,

   DECODE(at.caps_code, ph.caps_code, NVL(letter_of_credit,0) + adj_letter_of_credit, NULL, letter_of_credit, adj_letter_of_credit) letter_of_credit,

   DECODE(at.caps_code, ph.caps_code, NVL(electronic_fund_transfer,0) + adj_electronic_fund_xfer, NULL, electronic_fund_transfer, adj_electronic_fund_xfer) electronic_fund_transfer,

   DECODE(at.caps_code, ph.caps_code, NVL(food_stamps,0) + adj_food_stamps, NULL, food_stamps, adj_food_stamps) food_stamps,

   DECODE(at.caps_code, ph.caps_code, NVL(credit_others,0) + adj_credit_others, NULL, credit_others, adj_credit_others) credit_others

   FROM payment_history ph, audit_trail_mv at    WHERE ph.payment_history_key = at.payment_history_key(+);

Thanks a lot......

Arthur Received on Thu May 02 2002 - 14:39:21 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US