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]




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