Error: Update statement for volatile table

[acaireta]

Hello,

Can you help me please with the following script gives me the exception 'SAP DBTech JDBC: [7] (at 200): feature not supported: update statement for volatile table: line 8 col 11 (at pos 200) '

 DROP PROCEDURE EXAMPLE;
 CREATE PROCEDURE EXAMPLE(in var1 nvarchar(1))
 LANGUAGE SQLSCRIPT AS
 BEGIN
           CREATE LOCAL TEMPORARY TABLE #TMP_XX1 (var1 nvarchar(1));
           INSERT INTO #TMP_XX1 SELECT :var1 FROM DUMMY;
         UPDATE #TMP_XX1 SET var1 = '-';
          SELECT * FROM #TMP_XX1;
          DROP TABLE #TMP_XX1;
 END;

However if I call with the EXEC UPDATE works.

 DROP PROCEDURE EXAMPLE;
 CREATE PROCEDURE EXAMPLE(in var1 nvarchar(1))
 LANGUAGE SQLSCRIPT AS
 BEGIN
           CREATE LOCAL TEMPORARY TABLE #TMP_XX1 (var1 nvarchar(1));
           INSERT INTO #TMP_XX1 SELECT :var1 FROM DUMMY;
          EXEC 'UPDATE #TMP_XX1 SET var1 = ''-''';
          SELECT * FROM #TMP_XX1;
          DROP TABLE #TMP_XX1;
 END;

Why I can not call UPDATE to temporary table directly without the EXEC syntax?

Currently I have HANA PLATFORM Rev. 53.

Thanks very much.

[rindia · Accepted solution]

Hi Adria,

When I pasted your code, it executed successfully. I am on revision 58. Looks like it works properly with higher versions.

 

Regards

Raj

[acaireta · Accepted solution]

Thank you very much for your quick partnership.

For now I'll leave it to upgrade to higher version.