Re: SQLPLUS question

Thomas J Kyte (tkyte_at_us.oracle.com) wrote:
: 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 loop;
 

: 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

I think that Saad Ahmad's sqlplus stmt is fine as long as you have the appropriate indexes( like the account number). C programs is for "high-power"ed Oracle "nerds" -- use it only if you have the people to understand and support it.

MY 2 cents.

Sai

-- 
Sai Lim 
sylim_at_netcom.com 	or 	slim_at_geometric.com