02-06-12, 01:50 #1Registered User
- Join Date
- Jan 2012
Unanswered: Query on cursor usage in SQL Stored Procedure
I am finding some difficulties in the code below:
P2: declare cursor1 CURSOR for
CASE WHEN SUM(METER_RESET) >= 1 THEN 'Y' ELSE 'N' END AS METER_RESET_var,
CASE WHEN SUM(METER_ESTIMATED) >= 1 THEN 'Y' ELSE 'N' END AS METER_ESTIMATED_var,
SUM(CONSUMPTION) AS consumption
CASE WHEN METERRESET = 'Y' THEN 1 ELSE 0 END AS METER_RESET,
CASE WHEN METER_ESTIMATED = 'Y' THEN 1 ELSE 0 END AS METER_ESTIMATED, CONSUMPTION
WHERE METERUID IN (METER_STRING) AND SUM_HOUR=hour_lt AND SUM_DATE=date_lt
select meter into initial from netcool.meter;
set meter_var1=initial|| meter_var1||','|| initial || meter_var|| initial;
set i =i+1;
fetch cursor1 into meter_reset_cursor,meter_estimated_cursor,cons;
In the above query, I am using IN class and specifyng the varchar variable "meter_string". The varchar variable contains string like ('M1','M2'). Now the problem is I have to set the "meter_string" variable and then use it in declare cursor stmt. But since declration of cursor has to be done at the begginning according to the stored procedure syntax, I am facing problem in callin that varchar variable "meter_string". Can you please tel me the solution for this??
Thanks & Regards
02-06-12, 12:13 #2Registered User
- Join Date
- Jan 2007
- Jena, Germany
METER_STRING is a single string - it is not a set of values. Now you test that METERID is equal to the single string value "'M1','M2'".
What you need is either dynamic SQL - some approach that parses the METER_STRING values into a table comprised of multiple rows, where each row has one of the string values you want to compare against.Knut Stolze
IBM DB2 Analytics Accelerator
IBM Germany Research & Development
02-06-12, 12:23 #3Registered User
Now the problem is I have to set the "meter_string" variable and then use it in declare cursor stmt. But since declration of cursor has to be done at the begginning according to the stored procedure syntax, ...
- Join Date
- Feb 2008
The values used for variables in a cursor would be the values at the time of OPEN CURSOR.
After declared a cursor, you have enough time to set the values of variables used in the cursor until execution of open cursor.