Home » SQL & PL/SQL » SQL & PL/SQL » Materialized View (10g)
Materialized View [message #343613] |
Wed, 27 August 2008 11:32  |
ehegagoka
Messages: 493 Registered: July 2005
|
Senior Member |
|
|
Hi,
I'm trying to create a materialized view based on the query given, I tried this to create the view:
create materialized view cball_accounts_mv
build deferred
refresh force
on demand as
SELECT cc.currency_code, c.segment1 company, c.segment2 ACCOUNT,
cp.counterparty_short, cp.counterparty_long, b1.NAME ext_trade_book,
b1.accounting_treatment ext_book_acct_treatment,
b1.accounting_treatment_ceg ext_book_acct_treatment_ceg,
b2.NAME int_trade_book,
b2.accounting_treatment int_book_acct_treatment,
b2.accounting_treatment_ceg int_book_acct_treatment_ceg,
f.source_pnl_eti secdbid, f.source_pnl_header_id pnl_id,
f.delivery_month del_mth, f.source_pnl_adjustment_id adj_id,
f.source_stl_expense_id exp_id, trunc(ex.SETTLE_DATE) EXP_SETTLE_DATE,
f.source_pnl_mli_id man_id,
SUM (NVL (f.native_debit_amount, 0) - NVL (f.native_credit_amount, 0)
) native_net,
SUM (NVL (f.func_debit_amount, 0) - NVL (f.func_credit_amount, 0)
) usd_net
FROM cball.gl_code_combination_dim@crysu_link.ceg.corp.net c,
cball.currency_dim@crysu_link.ceg.corp.net cc,
cball.source_dim@crysu_link.ceg.corp.net s,
cball.gl_detail_fact@crysu_link.ceg.corp.net f,
cball.book_dim@crysu_link.ceg.corp.net b1,
cball.book_dim@crysu_link.ceg.corp.net b2,
cball.trade_header@crysu_link.ceg.corp.net t,
cball.counterparty_dim@crysu_link.ceg.corp.net cp,
cball.expense_header_dim@crysu_link.ceg.corp.net ex
WHERE f.source_dim_id = s.source_dim_id
AND f.currency_dim_id = cc.currency_dim_id
AND f.gl_code_combination_dim_id = c.gl_code_combination_dim_id
AND b1.book_dim_id = t.book_fact_id1
AND b2.book_dim_id = t.book_fact_id2
AND t.trade_header_id = f.trade_header_id
and f.EXPENSE_HEADER_DIM_ID = ex.EXPENSE_HEADER_DIM_ID
AND cp.counterparty_dim_id = t.counterparty_dim_id
AND cast (c.segment1 as int) < 900
AND c.segment2 in ('A02010105','A02010109','L10101005','L10101009')
AND f.valuation_date <= '25Aug08'
GROUP BY cc.currency_code,
c.segment1,
c.segment2,
f.source_pnl_eti,
f.source_pnl_header_id,
f.delivery_month,
f.source_pnl_adjustment_id,
f.source_stl_expense_id,
ex.SETTLE_DATE,
f.source_pnl_mli_id,
b1.NAME,
b1.accounting_treatment,
b1.accounting_treatment_ceg,
b2.NAME,
b2.accounting_treatment,
b2.accounting_treatment_ceg,
cp.counterparty_short,
cp.counterparty_long
HAVING SUM (NVL (f.func_debit_amount, 0) - NVL (f.func_credit_amount, 0)) != 0
OR
SUM (NVL (f.native_debit_amount, 0) - NVL (f.native_credit_amount, 0)) != 0
It was created, then I do a
exec dbms_mview.refresh('CBALL_ACCOUNTS_MV');
It finished quickly but I dont see any data on the materialized view. Please advise what I might have miss in the process. Thanks!
|
|
|
Re: Materialized View [message #343664 is a reply to message #343613] |
Wed, 27 August 2008 15:11   |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
a materlized view will only have what the query returns. Run the query seperatly, does it return anything? If it doesn't then that explains why your MVIEW is empty.
|
|
|
|
Goto Forum:
Current Time: Mon Feb 17 22:18:40 CST 2025
|