Re: summing syntax

From: Tim X <timx_at_nospam.dev.null>
Date: Fri, 25 Sep 2009 18:48:09 +1000
Message-ID: <87ab0j76ly.fsf_at_lion.rapttech.com.au>

Brock <wade.brock_at_yahoo.com> writes:

> Thanks in advance for any clues! I'm trying to edit an existing stored
> procedure in Oracle and need to know how to code using SUM in this
> scenario:
>
>
> SELECT
>
> TableA.FieldA, //character
> TableA.FieldB //number
>
> Pseudocode: If FieldA = 'Virginia' then add that record's FieldB to
> running total
> then running total = TotalReturns
> FROM
> WHERE
SELECT sum(fieldb)
FROM tableA
where fielda = 'Virginia';

and if you wanted to sum all the different fielda, you could do

SELECT sum(fieldb) total, fielda state
from tablea
group by fielda;

where 'total' is an alias for the sum field and state is an alias for fielda. Using the aliases may then make your plsql code more readable, especially if you process the result set using something like a for loop

for rec IN my_cursor loop

    my_procedure(rec.total, rec.state);
end loop;

where my_procedure is a plsql procedure that takes two arguments, a number (total) and a varchar2 (state). 'rec' can be any legit symbol name to represent each record in the result set from the cursor. you access the individual fields using plsql 'dot' notation i.e. rec.fieldname and of course, you will want to have an 'exception' block which will either handle possible expected exceptions and do a raise for any others (or just doesn't use 'when others then' and only handles specific exceptions.

-- 
tcross (at) rapttech dot com dot au

Received on Fri Sep 25 2009 - 03:48:09 CDT