Then I think not, unfortunately.
HJR
"Arthur" <amerar_at_iwc.net> wrote in message
news:8b622eae.0205030444.65780d13_at_posting.google.com...
> Howard,
>
> I have version 8.1.5.......can I do what I want with 8.1.5???
>
> Arthur
>
>
>
> "Howard J. Rogers" <dba_at_hjrdba.com> wrote in message
news:<aasfs1$hq2$1_at_lust.ihug.co.nz>...
> > I believe nested materialized views are a new feature of Oracle 9i. Your
> > version would be....??
> >
> > Regards
> > HJR
> >
> >
> > "Arthur" <amerar_at_iwc.net> wrote in message
> > news:8b622eae.0205021139.4e97ef1c_at_posting.google.com...
> > > Hello,
> > >
> > > Can I create a materialized view off of one table? I am trying to
> > > create a nested materialized view with FAST ON COMMIT. My original
> > > attempt at a materialized view, joins both a table (payment_history)
> > > AND a view (audit_trail_view) in its FROM clause.
> > >
> > > It seems that I cannot use a view in a materialized view.
> > >
> > > So, in order to get around that, I figured I could create a nested
> > > materialized view. I am trying to create a materialized view on the
> > > audit_trail, and then I think I can create another materialized view
> > > which joins the payment_history and the audit_trail materialized view.
> > >
> > > These views are quite complex, but I'm getting nowhere fast. I get a
> > > ORA-12051 on the audit_trail when I try to create the materialized
> > > view. Any suggestions? The table layouts are below.......maybe
> > > someone has a suggestion?
> > >
> > > Thanks in advance to anyone who lends a helping hand.
> > >
> > > CREATE MATERIALIZED VIEW AUDIT_TRAIL_MV
> > > REFRESH FAST ON COMMIT
> > > AS
> > > SELECT payment_history_key, caps_code,
> > > sum(nvl(adj_amt_cash,0)) adj_amt_cash,
> > > sum(nvl(adj_amt_check,0)) adj_amt_check,
> > > sum(nvl(adj_amt_credit,0)) adj_amt_credit,
> > > sum(nvl(adj_int,0)) adj_int,
> > > sum(nvl(adj_pen,0)) adj_pen,
> > > sum(nvl(adj_fund_amt,0)) adj_fund_amt,
> > > sum(nvl(adj_letter_of_credit,0)) adj_letter_of_credit,
> > > sum(nvl(adj_electronic_fund_xfer,0))
> > > adj_electronic_fund_xfer,
> > > sum(nvl(adj_credit_others,0)) adj_credit_others,
> > > sum(nvl(adj_food_stamps,0)) adj_food_stamps,
> > > sum(nvl(adj_advice_of_credit,0)) adj_advice_of_credit
> > > FROM audit_trail
> > > GROUP BY payment_history_key, caps_code;
> > >
> > > ERROR at line 16:
> > > ORA-12051: ON COMMIT attribute is incompatible with other options
> > >
> > > CREATE MATERIALIZED VIEW payment_history_mv
> > > TABLESPACE data_payhis_mv PARALLEL
> > > BUILD IMMEDIATE
> > > REFRESH FAST ON COMMIT
> > > AS
> > > SELECT trandatetime, batchnum, trannum, ej1_seqnum, tranname_ind,
> > > misc13, amt_paid,
> > > DECODE(at.caps_code, ph.caps_code,
> > >
> >
DECODE(IS_A_DATE(misc13),0,ph.trandatetime,1,TO_DATE(misc13,'MM/DD/YYYY'),ph
> > .trandatetime),
> > >
> >
NULL,DECODE(IS_A_DATE(misc13),0,ph.trandatetime,1,TO_DATE(misc13,'MM/DD/YYYY
> > '),ph.trandatetime),
> > > 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_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_mv at
> > > WHERE ph.payment_history_key = at.payment_history_key(+);
> > >
> > >
> > > Thanks a lot......
> > >
> > > Arthur
Received on Fri May 03 2002 - 16:12:21 CDT
