topic Re: WHILE TO UPDATE LOCAL TEMPORARY TABLE in Application Development and Automation Discussions

WHILE TO UPDATE LOCAL TEMPORARY TABLE

former_member858819 — Tue, 30 May 2023 22:08:32 GMT

Hello everyone

I am working on a query which by means of 3 temporary tables is built.

The first two tables feed the third but I have no way of being able to translate from SQL to hana a while loop that updates one of the fields of the third temporary table, SAP tells me that it does not support the condition-

Could someone tell me how I can replace this cycle or how I would have to structure it.

DECLARE I smallint;

DECLARE FechaI date;

DECLARE FechaF date;

I := 0;

FechaI := '2022-01-01';

FechaF := '2022-12-31';

CREATE LOCAL TEMPORARY COLUMN TABLE #BASEVESAS (

) ;

CREATE LOCAL TEMPORARY COLUMN TABLE # BASEAAS (

) ;

CREATE LOCAL TEMPORARY COLUMN TABLE #ESTRLAS (SELECT * FROM (SELECT * FROM #BASEVESAS UNION ALL SELECT * FROM #BASEAAS ) AS T1 ORDER BY T1."GroupMask", T1."GrpLine");

WHILE :I < 5

DO UPDATE #ESTRUCTURAFINAL

SET #ESTRUCTURAFINAL."Monto" =

IFNULL(

(SELECT SUM(T0."Monto") FROM #ESTRLAS T0 WHERE T0."FatherNum" = #ESTRLAS ."Cuenta" AND T0."ProfitCode" = #ESTRLAS ."ProfitCode"), 0)

WHERE #ESTRUCTURAFINAL."Postable" = 'N';

I := :I + 1;

END WHILE;

ALL THE INSERTS ----

SELECT * FROM #ESTRLAS T1 LEFT OUTER JOIN OPRC T2 ON T1."ProfitCode" = T2."PrcCode" ORDER BY T1."GroupMask", T1."GrpLine";

DROP TABLE #BASEVES;

DROP TABLE #BASEA;

DROP TABLE #ESTRL;

Like this is the structure, can you help me!

Re: WHILE TO UPDATE LOCAL TEMPORARY TABLE

Yogananda — Wed, 31 May 2023 04:29:50 GMT

roseavi

Just give it a try ..
You can replace the while loop with a cursor in HANA. Cursors are used to retrieve data from the result set of a SELECT statement one row at a time, instead of retrieving the entire result set at once ¹.

Here is an example of how to use a cursor in HANA ²:

DECLARE cursor_name CURSOR FOR SELECT column1, column2 FROM table_name;
OPEN cursor_name;
FETCH NEXT FROM cursor_name INTO @variable1, @variable2;
WHILE @@FETCH_STATUS = 0
BEGIN
    -- Do something with the variables
    FETCH NEXT FROM cursor_name INTO @variable1, @variable2;
END;
CLOSE cursor_name;

Re: WHILE TO UPDATE LOCAL TEMPORARY TABLE

former_member858819 — Thu, 01 Jun 2023 10:46:10 GMT

Thank so much, it works