Based on the documentation, outer joins are allowed in a fast refresh.
"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:<ukj94mbv7f56e1_at_corp.supernews.com>...
> "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
Received on Fri Aug 02 2002 - 11:01:59 CDT
