sqlhana

SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near ":OT"


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)


Solution

  • 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.