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

"Brock" <wade.brock (AT) yahoo (DOT) com> a écrit dans le message de news: 0e3ceda7-aa52-4f45-80e2-f7a3c470e132...oglegroups.com...
sum(decode(fielda,'Virginia',fieldb))

Regards
Michel

Brock <wade.brock (AT) yahoo (DOT) com> writes:

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