Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Invalid Options in View
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_creditFROM audit_trail
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
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, 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