Re: SQLPLUS question

cm4201_at_ccub.wlv.ac.uk (P.Sheth) writes:

>i hope some expert can help me on this..
>
>I need to produce a report in SQLPLUS which prints out rows..
>easy so far.. but the final column has to be an accumulation of each of
>the previous rows.
>
>
>e.g.
>
>Account Debt Accumulated Debt
>-------------------------------------
>A01 21 21
>A02 40 61
>A03 50 111
>A04 90 201
>
>
>
>I cant get the final column to appear even though I've
>tried GROUP BY, COMPUTE, BREAK ON PRIOR etc
>
>I would appreciate any help.
>
>Pratik
>
>
>
>
>
>

select a.account "Account", a.debt "Dept", sum(b.debt) "Accumulated Dept" from accounts a, accounts b
where a.account >= b.account
group by a.account, a.debt
/

Won't say that it will run real fast for a large table but it'll get what you asked for (which may not be what you want).

You may find that a report writer (like Oracle Reports) or a PL/SQL block (like the following) or a C program is what you really want.

create or replace procedure show_account_info as

   accumulated_debt number default 0;
begin

   dbms_output.put_line( 'Account          Debt      Accumulated Dept' );
   dbms_output.put_line( '-------          ----      ----------------' );

   for x in ( select account, debt from accounts order by account ) loop
	accumulated_debt := accumulated_debt + x.debt;
        dbms_output.put( rpad( x.account, 16 ) );
        dbms_output.put( to_char( x.debt, '9999' ) || '     ' );
	dbms_output.put_line( to_char( accumulated_debt, '999999' ) );

end show_account_info;
/

Be sure to issue "set serveroutput on" in SQL*Plus or DBA before running. then enter "execute show_account_info" to run the procedure.

Tom Kyte
Oracle Government
tkyte_at_us.oracle.com Received on Wed Feb 22 1995 - 18:56:02 CET