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

________________________________________
From: Olsen Richard N. [db2udbdba-ezmlmshield-x17441175.[Email address protected]
Sent: Wednesday, 14 May 2008 3:18 a.m.
To: LazyDBA Discussion
Subject: SELECT statement in a stored procedure

I am trying to use either of the following SELECT statements (below) in
a stored procedure.



The purpose of the select is to obtain the sum of (E06_BUDGETED_UNITS *
E07_PDD_SVS_RATE) for all rows where E06_SERVICES_ID <> SERVICESID_0
(valid values for E06_SERVICES_ID are 156897,156898,156899, & 156900). I
should be getting a result of $36,892.00 but instead receive a null
value when testing (using stored procedure builder).



If I use "AND E06_SERVICES_ID = SERVICESID_0;" I do receive the correct
value of $1,649.04.

Is what I'm trying to do even possible since it involves more than one
row?

Should I try using a cursor to perform the sum? I'm already using a
cursor in the stored procedure and its working fine. Is it possible to
have a cursor within a cursor?



Any suggestions would be appreciated.



I am running DB2 V7.1 on Z/OS.


SELECT SUM(E06_BUDGETED_UNITS * E07_PDD_SVS_RATE) INTO AMOUNT
-- FROM SERDB.E06_SERVICES_T
FROM SERDB.TEMPE06_T
INNER JOIN SERDB.E07_PDD_SVS_RATE_T
ON E07_RATE_CHG_ID = E06_RATE_CHG_ID
INNER JOIN SERDB.E05_CONS_BUDGET_T
ON E05_CONSUMER_ID = E06_CONSUMER_ID
AND E05_BUDGET_ID = E06_BUDGET_ID
WHERE E06_BUDGET_ID = BUDGETID_0
AND E06_CONSUMER_ID = CONSUMERID_0
AND E06_SERVICES_ID <> SERVICESID_0;

Or

SELECT SUM(E06_BUDGETED_UNITS * E07_PDD_SVS_RATE)
INTO AMOUNT
-- FROM SERDB.E06_SERVICES_T
FROM SERDB.E05_CONS_BUDGET_T,
SERDB.TEMPE06_T,
SERDB.E07_PDD_SVS_RATE_T
WHERE E07_RATE_CHG_ID = E06_RATE_CHG_ID
AND E05_CONSUMER_ID = E06_CONSUMER_ID
AND E05_BUDGET_ID = E06_BUDGET_ID
AND E06_BUDGET_ID = BUDGETID_0
AND E06_CONSUMER_ID = CONSUMERID_0
AND E06_SERVICES_ID <> SERVICESID_0;





Thank you,









Richard N. Olsen

Database Administrator

SC Department of Disabilities & Special Needs

Phone: (803) 898-9811

Email: [Email address protected]





---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html

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