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 Thu May 02 2002 - 17:49:58 CDT
