Skip to Content
Sep 30, 2017 at 12:10 PM

Unable to create temporary table in a stored procedure or Calculation view in SAP HANA


I am trying to calculate the percentile value against each material. To perform the same, I am trying to populate the same by passing each material to a select statement using cursor and storing into a temporary table.The same is not working when I try to create using a Calculation view.

I get the following error

"Message : Repository: Encountered an error in repository runtime extension;Model inconsistency. Deploy Calculation View: SQL: sql syntax error: incorrect syntax near "DECLARE": line 10 col 1 (at pos 360) Set Schema DDL statement: set schema "SYSTEM" Type DDL: create type "_SYS_BIC"."KUMARAIN/ZMLT_USECASE/proc/tabletype/VAR_OUT" as table ("YGMATRL" VARCHAR(20), "QUEUELADDAYS" DECIMAL) Procedure DDL: create procedure "_SYS_BIC"."KUMARAIN/ZMLT_USECASE/proc" ( OUT var_out "_SYS_BIC"."KUMARAIN/ZMLT_USECASE/proc/tabletype/VAR_OUT" ) language sqlscript sql security definer reads sql data as /********* Begin Procedure Script ************/ BEGIN create global temporary table "SYSTEM"."MAT_PERCENT" ( YGMATRL VARCHAR(20), PERCENTILE_VAL VARCHAR(4) ); DECLARE V_MAT_GRP VARCHAR(20); DECLARE Q_DAYS INTEGER; DECLARE CURSOR CURSOR_MATERIAL FOR SELECT DISTINCT YGMATRL FROM "_SYS_BIC"."COATS.POC/ZMLTTST" ORDER BY 1 ASC; OPEN CURSOR_MATERIAL; FETCH CURSOR_MATERIAL INTO V_MAT_GRP; SELECT "QUEUELADDAYS" into Q_DAYS FROM (SELECT "YGPRODORD", "QUEUELADDAYS", ROW_NUMBER() OVER (ORDER BY QUEUELADDAYS ASC) ROWNUM FROM "_SYS_BIC"."COATS.POC/ZMLTTST" WHERE "YGMATRL" = V_MAT_GRP ) RANK INNER JOIN (SELECT CAST(COUNT("YGPRODORD")*0.95 AS INTEGER) IX FROM "_SYS_BIC"."COATS.POC/ZMLTTST" WHERE "YGMATRL" = V_MAT_GRP) CNT ON RANK.ROWNUM = CNT.IX; INSERT INTO "_SYS_BIC"."MAT_PERCENT" VALUES (V_MAT_GRP,Q_DAYS); CLOSE CURSOR_MATERIAL; var_out = SELECT * FROM "_SYS_BIC"."MAT_PERCENT" ; DROP TABLE "_SYS_BIC"."MAT_PERCENT" ; END /********* End Procedure Script ************/"