Skip to Content
avatar image
Former Member

declare cursor on local temporary table


Hello Experts,

i am  creating a procedure where in i build my source data first and feed it in a LTT . after feeding it to LTT i need to iterate on the data hence i need to use LTT in cursor select statement. in the beginning while declaring cursors i was getting syntax errors which resolved completely when i moved the same declare cursor statement jsut before to CREATE LTT statement (wiered) like below

BEGIN

decare var1 varchar(2);

declare cursor c1 for select ........;

create local temporary table ltt1 (........);

PROBLEM:- now as i need to use the LTT in my cursor select statement , if i declare the cursor before LTT create statment i get the error "cannot use the LTT table "table_name" which is obvious and if i give the same declare cursor statement post to my LTT create statement i get invalid syntax error at the Declare cursor statement......

can anyone suggest please if i can use LTT somehow in my cursor and also why procedure does n't let me  declare my cursor after the create statement !!

thanks for knowledge sharing....:)

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Mar 11, 2015 at 06:22 AM

    As you cannot declare a cursor on a local temporary table, you can read the data directly and convert it into an array with the ARRAY_AGG function. Then you can loop over the array, similar to the usage of a cursor.

    I have used this to build a procedure which resets sequences with the help of the reset_by_query. I have defined a cursor over the sys.sequences table which reads the reset_by_query. Then I execute all the reset_by_queries and write the data into a local temporary table, as dynamic sql does not have a return parameter. Afterwards I read from the local temporary table, convert the data into an array, loop over the array and execute an alter sequence statement with the new determined restart value of the reset_by_query. At last I could drop the local temporary table.

    Regards,

    Michael

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 22, 2015 at 05:25 AM

    seems like i cannot declare cursor on LTT... found out an alternate way !

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      I thought about a similar solution. In case someone needs the code for it, this works:

      my_records_with_index =
          SELECT FIELD1, FIELD2, FIELD3,
                  ROW_NUMBER() over (partition by PARTITION_COL) as INDEX
          FROM (
            SELECT FIELD1, FIELD2, FIELD3, 1 AS PARTITION_COL
            FROM #MY_TEMP_TABLE
            );
      
        SELECT MAX(INDEX) INTO v_max_index FROM :my_records_with_index;
      
        --iterate over each location
        FOR v_index IN 1 .. :v_max_index DO
        
            SELECT FIELD1, FIELD2, FIELD3
            INTO v_field1_aux, v_field2_aux, v_field3_aux
            FROM :my_records_with_index
            WHERE INDEX = :v_index
            LIMIT 1;
      
            --do whatever you need with v_field1_aux, v_field2_aux, v_field3_aux...
      
      END FOR;
      
  • avatar image
    Former Member
    Jun 15, 2015 at 02:57 PM

    I had the same issue and figured out a easier approach than using ARRAY_AGG, and also an approach that will work with older revision of hana.

    The trick is to use a begin/end. Declare must be before any instruction after the create procedure top bgin, but you can mitigate this by using a additionnal begin/end. They still need to be before any other instruction in this code block, but as you can put this code block anywhere, it works.

    here is an example :

    CREATE procedure test()

    as

    BEGIN

         decare var1 varchar(2);

         create local temporary table ltt1 (........);

         begin

              declare cursor c1 for select from ltt1

              --rest of code

         end;

    end;

    Add comment
    10|10000 characters needed characters exceeded