on 03-10-2017 9:45 AM
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
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?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
88 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.