VAR_OUT inside cursor

[Former Member]

Hello,

I need to calculate cumulative stocks. I want to do it using SAP HANA analysis process. I created test procedure with cursor and VAR_OUT  inside cursor. When i call this procedure i always get only one value. How i can get more then one value from this procedure?

DECLARE CURSOR c_cursor1 FOR

Select "SUPNUM" FROM "_SYS_BIC"."Stocks/STOCKS"

                              WHERE "SUPNUM" IN ('00000028345', '00000039053')

GROUP BY "SUPNUM";

FOR cur_row as c_cursor1 DO

     VAR_OUT = Select "SUPNUM" FROM "_SYS_BIC"."Stocks/STOCKS"

                           WHERE "SUPNUM" = cur_row.SUPNUM GROUP BY "SUPNUM";

END FOR

[Former Member · Accepted solution]

Hi Sergey,

If you want to retain the cursor(though you could try the same with JOINS), you can consider using arrays.

DECLARE CURSOR c_cursor1 FOR

                           SELECT "SUPNUM" FROM "_SYS_BIC"."Stocks/STOCKS"

WHERE "SUPNUM" IN ('00000028345', '00000039053')

                           GROUP BY "SUPNUM";

DECLARE AR_SUPNUM   VARCHAR(10) ARRAY;

FOR cur_row as c_cursor1 DO

         V_COUNT := V_COUNT + 1;

     Select "SUPNUM" FROM "_SYS_BIC"."Stocks/STOCKS" INTO V_SUPNUM

         WHERE "SUPNUM" = cur_row.SUPNUM GROUP BY "SUPNUM";

        AR_SUPNUM[:V_COUNT] =  :V_SUPNUM;

END FOR

VAR_OUT = UNNEST(:AR_SUPNUM)

Nehal.

[Former Member · Accepted solution]

Thank you, Nehal!

Your code is working.