You will get a null when no rows qualify.


Sincerely,
Darrin Preble / DataDirect Technical Support
[Email address protected]
http://www.datadirect.com/support
US Tel: 1-281-491-4200
UK Tel: +44 (0)1753 218949


-----Original Message-----
From: Olsen Richard N.
[mailto:db2udbdba-ezmlmshield-x17441175.[Email address protected]
Sent: Tuesday, May 13, 2008 10:18 AM
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