Skip to Content

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

Sep 30, 2017 at 12:10 PM


avatar image

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 ************/"

10 |10000 characters needed characters left characters exceeded

1) Please confirm if we can use Temporary table in Stored Procedure or Calculation View

2) Please confirm if the attached script has any issuessp-backup.txt

sp-backup.txt (1.2 kB)
* Please Login or Register to Answer, Follow or Comment.

0 Answers