Skip to Content
0

Scripted calculation view not working

Mar 10, 2017 at 09:45 AM

49

avatar image

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

10 |10000 characters needed characters left characters exceeded

Hello,
Any inputs on above?

Br

sd

0
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Lars Breddemann
Mar 15, 2017 at 07:46 AM
0

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.

Share
10 |10000 characters needed characters left characters exceeded
sumeet durgia Mar 15, 2017 at 11:36 AM
0

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?

Share
10 |10000 characters needed characters left characters exceeded