You could create only one view
create or replace view vw_budget_balance as
select b.year, b.CostCenter, max(b.budget) budget,
nvl(sum(r.expense),0) expenses, max(b.budget) -
nvl(sum(r.expense),0)
balance
from request r, budget b
where r.year(+) = b.year
and r.CostCenter(+) = b.CostCenter
group by b.year,b.costcenter;
-----Original Message-----
Alan Kong
Sent: Friday, August 15, 2008 5:41 AM
To: Multiple recipients of list ODTUG-SQLPLUS-L
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: kkkong ee.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).
--
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: Don Biddle
INET: technicalesitech.com
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).
|
