can you post your script here please ?
Joseph
Hi Joseph,
Here is stored procedure
CREATE PROCEDURE SP_CAL_DISC( IN V_KUNNR VARCHAR(10) , IN V_AMT INTEGER) , OUT V_DISC INTEGER )
LANGUAGE SQLSCRIPT AS
V_ LAND1 VARCHAR(2);
BEGIN
-- Get Status
SELECT " LAND1" INTO V_ LAND1 FROM "ECC6"."KNA1"
WHERE "KUNNR" = :V_KUNNR ;
IF :V_ LAND1 = 'US' THEN
V_DISC := : V_AMT * 0.2;
ELSE
V_DISC := : V_AMT * 0.1;
END IF;
END;
Now I want to use it in calculation view, but it does not work.
SQLA_VIEW = SELECT [ "MANDT",
"KUNNR",
SUM("NETWR ") AS NETWR,
"WAERK",
FROM "ECC6","VBAK"
GROUP BY
"MANDT",
"KUNNR",
"WAERK";
var_out = CE_PROJECTION (:SQLA_VIEW ,
[ "MANDT",
"KUNNR",
"NETWR",
"WAERK",
CE_CALC(CALL SP_CAL_DISC("KUNNR","NETWR",ZDISC),INTEGER)) AS ZDISC ] );
Hello Pravin,
It appears to me you are using both both SQL and CE built in function in the below statement,
*** SQLA_VIEW = SELECT [ "MANDT", ***
I have adjusted it little bit, try to use this...
SQLA_VIEW = CE_COLUMN_TABLE [ "MANDT",
Goodluck,
Vinay Karanam
Hi Pravin,
The approuch is not supported, you can't call a produce inside CE_CALC, maybe in future at least L functions can be called.
To move forward the best option for you is adjust the SP_CAL_DISC procedure to accept a table type with all customers at once, processing in lot and sending it back... On the calling routine you need to LEFT JOIN this result with the original source.
See it in general (I didn't tested syntax):
create type tt_cal_disc_in as table ( MANDT VARCHAR(3), KUNNR VARCHAR(10),
NETWR INTEGER,WAERK VARCHAR(10));
create type tt_cal_disc_out as table ( KUNNR VARCHAR(10), WAERK VARCHAR(10),DISC INTEGER);
create procedure sp_cal_disc( in input tt_cal_disc_in, out output tt_cal_disc_out ) language sqlscript as
begin
output = select
intab.kunnr,
intab.waerk,
case when ifnull(kna1.land1,'') = 'US' then :intab.netwr * 0.2 else :intab.netwr * 0.1 end as disc
from :input as intab left outer join ecc6.kna1 as kna1
on kna1.mandt = intab.mandt
and kna1.kunnr = intab.kunnr;
end;
Inside your calc view, you need to prepare data, call procedure, join result to original data...
call sp_cal_disp( :SQLA_VIEW , lt_result );
lt_result = CE_LEFT_OUTER_JOIN(:SQLA_VIEW,:lt_result,["MANDT","KUNNR","WAERK"]);
var_out = CE_PROJECTION (:lt_result,
["MANDT",
"KUNNR",
"NETWR",
"WAERK",
"DISC"]);
BTW: Review the datatypes you posted, not sure about NETWR as integer.
Regards, Fernando Da Ros
Hi Pravin
As per my knowledge there is no possibility to use stored procedure to build calculated attributes ,If it is there most of the data cleansing job can be done easily. the grammar which we use in calculated attributes itself is different .You can go through the below thread to see what all we can use as part of calculated attributes ,at the last Fernando provided the pointer which would be helpful,you can try that .But I doubt the possibility of using Stored procedure .Awaiting your feedback
http://scn.sap.com/thread/3187022
Thanks
Santosh Varada
Hi,
Thanks a lot.
As of now, I have created a separate parts and merged them with join as directed by Fernando.
Regards,
Pravin
