Skip to Content
0
Dec 21, 2022 at 02:09 AM

Inserting and CRUD operation on temp tables hana sql

288 Views Last edit Dec 21, 2022 at 04:16 AM 3 rev

new to hana sql - appreciate if someone can give me some simple samples

my aim is to create all these operations ultimately in a stored proc. I know how to create a proc however

I am trying these first on Hana studio

having trouble doing the following

1 - I need to create a temp table I tired with following below and it succeeds

DROP TABLE #Key_POLinesTbl;

CREATE LOCAL TEMPORARY ROW TABLE #Key_POLinesTbl ( 
ShipDate DATE NULL , 
ItemCode VARCHAR(50) NULL, 
DocEntry NUMERIC (16) NULL);<br>

2 however when trying this code it fails

INSERT INTO #Key_POLinesTbl VALUES 
( SELECT L."ShipDate"  ,   L."ItemCode"   , L."DocEntry"  
  FROM POR1 L 
  WHERE L."LineStatus" = 'O'
 ORDER BY  L."ItemCode", L."ShipDate" DESC  )
 ;<br>

the error I get is

Statement 'CREATE LOCAL TEMPORARY ROW TABLE #Key_POLinesTbl ( ShipDate DATE NULL , ItemCode VARCHAR(50) NULL, ...' 
successfully executed in 7 ms 174 µs  (server processing time: 5 ms 875 µs) - Rows Affected: 0 
Could not execute 'INSERT INTO #Key_POLinesTbl VALUES ( SELECT L."ShipDate" , L."ItemCode" , L."DocEntry" FROM POR1 L ...'
SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near "SELECT": line 2 col 3 (at pos 40)<br>

ultimately I want to be able to read the data in the table - manipulate it ( delete some rows , and update etc) and then get a final result set out of the procedure ( in a select * statement)

I am also concerned abut permission levels on ROW tables vs Column tables. So if anyone knows any infor on that area would be glad to get to know.