Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2012
    Posts
    2

    Unanswered: Query on cursor usage in SQL Stored Procedure

    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

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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, ...
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •