Alan,
I assumed you wanted one record for each year and costcenter
that is why I
grouped the SQL by year and costcenter. When you group
records in a SQL
statement each non-grouping column in the SELECT clause such
as budget or
expense needs a grouping function for the SQL to execute.
Even though the
budget for a given year and costcenter will be the same you
still need to
use the function "max" so the SQL will execute
otherwise you will get a
syntax error.
Don
-----Original Message-----
Alan Kong
Sent: Thursday, August 21, 2008 1:35 AM
To: Multiple recipients of list ODTUG-SQLPLUS-L
Dear Don,
I don't understand why we use "max" in the query
when creating the view?
Could you please elaborate more?
Thank you.
Regards
Alan
On 8/15/2008 11:00 PM, Don Biddle wrote:
> 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: 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).
--
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).
|
