Home » SQL & PL/SQL » SQL & PL/SQL » Materialized View (10g)
Materialized View [message #343613] Wed, 27 August 2008 11:32 Go to next message
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 Go to previous messageGo to next message
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.
Re: Materialized View [message #343775 is a reply to message #343613] Thu, 28 August 2008 02:11 Go to previous message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Is your select query returning data?

What I would perhaps do is I would create a view for query
and Create Materialized view on the view.


May be helpful for you:
http://en.wikipedia.org/wiki/Materialized_view


Regards,
Oli

[Updated on: Thu, 28 August 2008 02:14]

Report message to a moderator

Previous Topic: Related to Select.....Functions
Next Topic: Oracle Partitioning
Goto Forum:
  


Current Time: Mon Feb 17 22:18:40 CST 2025