MV Error Message

From: Arthur <amerar_at_iwc.net>
Date: 1 Aug 2002 11:45:57 -0700
Message-ID: <8b622eae.0208011045.5c3f368c@posting.google.com>

Hello All,

Here is the error message:

ORA-12015: cannot create a fast refresh snapshot from a complex query

And here is the query. Can someone tell me what is so complex about this that I cannot FAST REFRESH it?

CREATE MATERIALIZED VIEW payment_history_mv

      PARALLEL
      BUILD IMMEDIATE
      REFRESH FAST

AS
SELECT ph.rowid ph_rowid, at.rowid at_rowid, trandatetime, batchnum, trannum, ej1_seqnum, tranname_ind,

       teller, misc13, misc14,
 DECODE(IS_A_DATE(misc13),0,ph.trandatetime,1,TO_DATE(misc13,'MM/DD/YYYY'),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_paid,0) + NVL(adj_amt_cash,0) + NVL(adj_amt_check,0) + NVL(adj_amt_credit,0), NULL, amt_paid, NVL(adj_amt_cash,0) + NVL(adj_amt_check,0) + NVL(adj_amt_credit,0)) amt_paid,
 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_summary at  WHERE ph.payment_history_key = at.payment_history_key(+);

Thank you,

Arthur Received on Thu Aug 01 2002 - 13:45:57 CDT