Message-ID: <3D529448.B6C3EADE@d2mail.de> Disposition-Notification-To: Martin Haltmayer Date: Thu, 08 Aug 2002 17:54:48 +0200 From: Martin Haltmayer X-Mailer: Mozilla 4.78 [en] (Windows NT 5.0; U) X-Accept-Language: en,de-DE MIME-Version: 1.0 Newsgroups: comp.databases.oracle.server To: Arthur Subject: Re: MV Error Message References: <8b622eae.0208011045.5c3f368c@posting.google.com> <8b622eae.0208020801.13eca12b@posting.google.com> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Lines: 117 X-Authenticated-User: vikivesi X-Comments: This message was posted through Spamkiller.Newsfeeds.com X-Comments2: IMPORTANT: Newsfeeds.com does not condone, nor support, spam or any illegal or copyrighted postings. X-Comments3: IMPORTANT: Under NO circumstances will postings containing illegal or copyrighted material through this service be tolerated!! X-Report: Please report illegal or inappropriate use to You may also use our online abuse reporting from: http://www.newsfeeds.com/abuseform.htm X-Abuse-Info: Please be sure to forward a copy of ALL headers, INCLUDING the body (DO NOT SEND ATTACHMENTS) Organization: Newsfeed.com http://www.newsfeed.com 100,000+ UNCENSORED Newsgroups. Path: news.easynews.com!easynews!newsfeed.frii.net!newsfeed.frii.net!news-out.newsfeeds.com!l2!spamkiller.newsgroups.com!not-for-mail Xref: easynews comp.databases.oracle.server:157280 X-Received-Date: Thu, 08 Aug 2002 08:49:58 MST (news.easynews.com) Hi Arthur, consider the following example: ---------------------------------------------------------------------------------------- SQL> create table bla1 (n number, a varchar2 (30)); 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" wrote in message news:... > > "Arthur" wrote in message > > news: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 > > > > Quite obvious: you have numerous calculated expressions, including decodes, > > and you have an outer join between two tables. > > > > Regards