I tested the code in the document about HANA version 2.3.42, but it got an error. I don't know how to fix it. Hope you help. This is link: https://help.sap.com/viewer/de2486ee947e43e684d39702027f8a94/2.0.02/en-US/36f718be73e745ceae0530aa80850ce3.html
CREATE PROCEDURE ADD_SUM(IN IT TAB, OUT OT TAB) AS
BEGIN
DECLARE IDX INT = 0;
DECLARE K VARCHAR(20) = '';
DECLARE VSUM INT = 0;
IF IS_EMPTY(:IT) THEN
RETURN;
END IF;
OT = SELECT * FROM :IT ORDER BY K;
WHILE :OT.K[IDX + 1] IS NOT NULL DO
IDX = IDX + 1;
IF :OT.K[IDX] <> K THEN
IF K <> '' THEN
:OT.INSERT(('Sum ' || K, VSUM), IDX);
IDX = IDX + 1;
END IF;
:OT.INSERT(('Section ' || :OT.K[IDX], 0), IDX);
IDX = IDX + 1;
K = :OT.K[IDX];
VSUM = 0;
END IF;
VSUM = VSUM + :OT.V[IDX];
END WHILE;
:OT.INSERT(('Sum ' || K, VSUM), IDX + 1);
END
CALL ADD_SUM(TAB, ?);
This is an error:
Could not execute 'CREATE PROCEDURE ADD_SUM(IN IT TAB, OUT OT TAB) AS BEGIN DECLARE IDX INT = 0; DECLARE K VARCHAR(20) ...' SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near ":OT": line 16 col 2 (at pos 322)
It’s generally a good idea to check whether the referenced documentation is the right one for the HANA version in use. HANA 2 has many more and extended features than HANA 1.
The .insert
syntax for table variables as shown in the example code is one of those added features in HANA 2.
The documentation also has a section “what’s new” that covers the changes between the different product versions.