Hi All,
I am finding some difficulties in the code below:

P2: declare cursor1 CURSOR for
SELECT
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
FROM
(
SELECT
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
FROM GEMS.METER_CONS_SUM_1H_NEW
WHERE METERUID IN (METER_STRING) AND SUM_HOUR=hour_lt AND SUM_DATE=date_lt
);
SET length1=cardinality(meterid_list);
select meter into initial from netcool.meter;
set meter_var1='';
set char_len=0;
SET sum_consumption=0.0;

while(i<=length1)
do
set meter_var=meterid_list[i];
set meter_var1=initial|| meter_var1||','|| initial || meter_var|| initial;
set char_len=LENGTH(meter_var1);
set i =i+1;
set meter_string=substr(meter_var1,4,char_len);
end while;

open cursor1;
fetch cursor1 into meter_reset_cursor,meter_estimated_cursor,cons;
set meter_reset_result=meter_reset_cursor;
set meter_estimated_result=meter_estimated_cursor;
set sum_consumption=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
Lavanya

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.

No problem.

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.