Re: question on getting cumulative % and other

One possibility for the query - using the 'Analytic functions' of 8.1.6.

SQL> create table dept_demo (
  2 dept number, revenue number);

SQL> insert into dept_demo values (1,100);
SQL> insert into dept_demo values (2,200);
SQL> insert into dept_demo values (3,300);
SQL> insert into dept_demo values(4,400);

select
 dept,
 cum_rev,
 100 * sum(pct) over(
  order by dept
 ) cum_pct
from
 (
 select
  dept,
  sum(revenue) over (
   order by dept
  ) cum_rev,
  ratio_to_report(revenue) over(
  ) pct
 from dept_demo
 )
order by dept

      DEPT CUM_REV CUM_PCT
---------- ---------- ----------

         1        100         10
         2        300         30
         3        600         60
         4       1000        100

For the trigger on the transaction table - you do not want to do that, as it is likely to lead to a lot of performance problems. Imagine one person does a dept1 transaction, then spends 5 minutes looking up detail without committing. Whilst this is going on, any other transaction on dept 1 has to wait because the first transaction will be holding a lock on the dept1 row of the summary table.

Read up on materialized views - if your summary is allowed to be a little out of data, a 'fast refresh' using a snapshot log and snapshot processes may be adequate. Alternatively there is an 'update on commit' option for materialized views.

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases

Publishers:  Addison-Wesley
Book bound date: 8th Dec 2000
See a first review at:
http://www.ixora.com.au/resources/index.htm#practical_8i
More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



Hongjiew wrote in message <20001126191217.23338.00002100_at_ng-fv1.aol.com>...


>Dear friends:

>

>I try to accomplish the following:

>

>table

>

>department  revenue perctage

>1                100         0.10

>2                200         0.20

>3                400         0.40

>...

>I want to write a simple query to generate the cumulative percentage,

>

>In other words,

>department  revenue     cum

>1                100            0.10

>2                300            0.30

>3                700            0.70

>...

>

>Also, I like to create a triger on a transaction table such that it will

 invoke


>a procedure which in term is updating a summary table based on the

 transaction


>table.

>

>Please help.

>

>Thanks.

>

>hongjiew_at_aol.com