Rick,

Try it -- sum of a column whose value is null is ignored.

select id, comm from staff where id in (20,30);
---------+---------+---------+---------+---------+---------+-
ID COMM
---------+---------+---------+---------+---------+---------+-
20 612.45
30 ---------
DSNE610I NUMBER OF ROWS DISPLAYED IS 2
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+-
select sum (comm), avg(comm), count(*)
from staff where id in (20,30);
---------+---------+---------+---------+---------+---------+-

---------+---------+---------+---------+---------+---------+-
612.45 612.4500000000 2




darrin


-----Original Message-----
From: Rick Vine
[mailto:db2udbdba-ezmlmshield-x3547119.[Email address protected]
Sent: Tuesday, May 13, 2008 3:30 PM
To: LazyDBA Discussion
Subject: RE: SELECT statement in a stored procedure

Don't forget the rules of maths: adding null to any number results in
null. So if any of the values contributing to the SUM are null then your
answer is null. Maybe you need to put NVL around the units (or rate) to
return a zero for the sum to work as you expect

cheers from NZ
Rick

DB2 & UDB email list listserv db2-l LazyDBA home page