cancel
Showing results for 
Search instead for 
Did you mean: 

Scripted calculation view not working

former_member226419
Contributor
0 Kudos

Hello Everyone,

I have one graphical view 'CA_BLT_LIKP_TEST' on which we have 2 filters 'IP_KUNNR' and 'IP_KUNAG' of type nvarchar(10). This view is working fine.

Now i have created one scripted calculation view 'CA_BLT_BWD_DELV_SCRIPT' and i am selecting the data from above view only with same filters but getting error 'Instantiation of calculation model failed'.

The same code if i use in external procedure its giving me output but if i use in scripted calculation view , its throwing me error. i also tried calling procedure with same code in scripted calculation view , its giving me same error. its seems very strange to me.

CALL "BI"."kgm.dtp.blt/CA_BLT_BWD_DELV_SCRIPT10" (:IP_KUNNR,:IP_KUNAG,var_out); --> Not working

Below is the code.

DECLARE IPPKUNNR NVARCHAR(5000);

DECLARE IPPKUNAG NVARCHAR(5000);

DECLARE IPKUNNR2 NVARCHAR(100) := ''''||:IP_KUNNR||'''';

var_out = select "KUNNR" , "KUNAG" , "VBELN" from "_SYS_BIC"."kgm.dtp.blt/CA_BLT_LIKP_TEST" (PLACEHOLDER."$IP_KUNNR$" => :IPKUNNR2); --> Throwing error.

But same I did in a stored procedure, its working absolutely fine.

create procedure "BI"."kgm.dtp.blt/CA_BLT_BWD_DELV_SCRIPT10/proc"
( IN IP_KUNNR NVARCHAR(10),
IN IP_KUNAG NVARCHAR(10),
OUT var_out TABLE
( KUNNR NVARCHAR(10),
KUNAG NVARCHAR(10),
VBELN NVARCHAR(10) ) )
language sqlscript sql security definer reads sql data as
/********* Begin Procedure Script ************/
BEGIN
DECLARE IPPKUNNR NVARCHAR(5000);
DECLARE IPPKUNAG NVARCHAR(5000);
DECLARE IPKUNNR2 NVARCHAR(100) := ''''||:IP_KUNNR||'''';
var_out = select "KUNNR" , "KUNAG" , "VBELN" from "_SYS_BIC"."kgm.dtp.blt/CA_BLT_LIKP_TEST" (PLACEHOLDER."$IP_KUNNR$" => :IPKUNNR2);

Br
Sumeet

former_member226419
Contributor
0 Kudos

Hello,
Any inputs on above?

Br

sd

0 Kudos

The code which is throwing error for you is not sqlscript. Also you cannot Declare variables in sql sheet and try to store values in it. These can be achieved only through Procedures in Hana

Accepted Solutions (0)

Answers (2)

Answers (2)

former_member226419
Contributor
0 Kudos

This stored procedure output I need to join with other master data calculation views in final view. That's why i need scripted view. Then how can i join my stored proc output with master data views?

lbreddemann
Active Contributor
0 Kudos

This is likely due to the way the SQL Studio editor for the scripted calc view works with your code.

The "quotation-monster" that you pulled at the declaration of IPKUNNR2 would be my first guess for a culprit here.

Since scripted calc views are deprecated anyhow, the recommendation for this is clear: implement this as a procedure or a table function.