Help on Query creating view

Hi,

Please help on SQL for creating view which produce the
Budget Balance 
based on Budget and Request table.

Budget Table:
**********

Year     CostCenter     Budget
2008      Center1          10000
2008      Cenetr2          15000
2008      Center3         20000
2007      Center1         ......
2007      Center2         ..........


Request Table:
*************

Year      CostCenter        Expense       Desciption
2008      Center1              50                Disk
2008      Center2              100              Food
2008      Center2                50              Drinks
2008      Center1                 10              ticket
2007      Center1                  5              ......
2007      Center2                10             .........
2007      Center3                  5             ..........

Budget Balance:
**********
When user enters "2007' as select criteria, the
following will be 
displayed in a Budget Balance Form.

Year 2007:

CostCenter          Budget         Expense        Balance
Center1               10000          60                 
9940
Center2               15000         150                
14850
Center3                20000                               
20000

What I did at the moment:
1) I use the Budget table(view) as base block;
2) 'pre-query' trigger will retrieve budgets based on '2007'
entered;
3) 'post-query' trigger will get sum of expenses and update
the form; ( 
This wil be inefficient as commented by Paul)
4) Budget Balance will be calculated based on Budget and
Expense Sum.

Can I create a view for budget balance as follow:

1) Create a view to produce Sum of request:

create or replace view vw_request_total as
  select year, CostCenter, description, sum(expense) as
subtotal
  from request
  group by year, CostCenter;

2) Then create a view for the budget balance:

create or replace view vw_budget_balance as
 select b.year, b.CostCenter, budget, r.subtotal,
budget-r.subtotal as 
balance
 from budget b, vw_request_total r
 where b.year = r.year(+) and
 b.CostCenter= r. CostCenter(+);

Could I create the budget_ balance view this way with 2
outer joins? Pls 
advise.

Thank you.

Regards
Alan




-- 
For more information on this topic or to become a member,
visit our Web site at http://www.ODTUG.com  

Join ODTUG for The PL/SQL and The APEX Expert Gathering of
the Year! OPP2008 and APEXposed! 2008 will be held on
October 29-30, 2008 at the Wyndham O'Hare, Chicago. Visit
www.odtugopp.com or www.odtugapextraining.com for more
details.


-- 
Author: Alan Kong
  INET: kkkongee.cuhk.edu.hk

Fat City Hosting, San Diego, California -- http://www.fatcity.com
------------------------------------------------------------
---------
To REMOVE yourself from this mailing list, send an E-Mail
message
to: ListGurufatcity.com (note EXACT spelling of 'ListGuru') and
in
the message BODY, include a line containing: UNSUB
ODTUG-SQLPLUS-L
(or the name of mailing list you want to be removed from). 
You may
also send the HELP command for other information (like
subscribing).