Re: MV Error Message

"Arthur" <amerar_at_iwc.net> wrote in message news:8b622eae.0208011045.5c3f368c_at_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

Quite obvious: you have numerous calculated expressions, including decodes, and you have an outer join between two tables.

Regards

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address