| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: MV Error Message
Hi Arthur,
consider the following example:
Table created.
SQL> create table bla2 (m number primary key, b varchar2 (30));
Table created.
SQL> create materialized view log on bla1 with rowid;
Materialized view log created.
SQL> create materialized view log on bla2 with rowid;
Materialized view log created.
SQL>
SQL> create materialized view blamv1
2 refresh fast
3 as
4 select t1.rowid as t1_rowid, t2.rowid as t2_rowid, t1.n, t1.a, t2.b
5 from bla1 t1, bla2 t2 where t1.n = t2.m (+);
Materialized view created.
SQL>
SQL> create materialized view blamv2
2 refresh fast
3 as
4 select t1.rowid as t1_rowid, t2.rowid as t2_rowid, t1.n, t1.a || t2.b as c
5 from bla1 t1, bla2 t2 where t1.n = t2.m (+);
from bla1 t1, bla2 t2 where t1.n = t2.m (+)
*
ERROR at line 5:
ORA-12015: cannot create a fast refresh snapshot from a complex query
Your problem is not the outer join but the functions you use within the column definitions. So leave away the decodes and the nvl etc. and it should work.
Martin
Arthur wrote:
>
> 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 Thu Aug 08 2002 - 10:54:48 CDT
 |
 |