Re: Invalid Options in View

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 - 07:44:57 CDT