declare cursor on local temporary table

[Former Member]

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....:)

[former_member184713]

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;

[michael_vogel1]

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

[Former Member]

nice  trick... will try to use it when i come across the same scenerio .. thanks Michael.

[Former Member]

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

[lbreddemann]

It would be really fancy if you'd share what your alternative way is 

[Former Member]

Well it wasn't a convetional way and was specific to my requirements. Did the record count and then iterated on the LTT.... filled my purpose....

[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;