Hi folks,
Is it possible to have a procedure in a SQL Script calculation view that calls other procedures, does some calculations, and then returns the values in the view output?
ie:
Call procedure 1 and get result
Call procedure 2 and get result
Then add the two results together and output?
I know I can create a CE_JOIN and I have experimented with various CE calculations but my question is whether it's possible to call other external procedures and return results in the view output?
Thanks,
-Patrick
Yes this works. I have used it mainly from reuse perspective of existing available procedures in Scripted Calc View and consume it from different clients.
However as documented http://help.sap.com/hana/hana_dev_sqlscript_en.pdf I would first try to exploit underlying engine capability and see if everything is possible using CE operators itself.
Hi Patrick,
SQL script calc view is also implemented as a procedure. For each Calc view a corresponding Procedure is created in SYS_BIC schema and a table type is defined for the VAR_OUT structure. The created procedure has READ only access on the data which means any data manipulation commands are not allowed.
So now theoretically it should be possible to call another procedure from one procedure and pass on the output. It is an interesting concept and I would like to try it tomorrow (no access to system on Sunday) and confirm you back.
Regards,
Ravi
The only restrictions I remember are regarding input parameters (as far as I understand, you cannot stack passing parameters from the outer calc view to the inner proc) and the fact that your inner proc needs to be read only.
There's some decent stuff to be found on SCN huh 😉. Above tip came in handy for me today, cheers.
Script calc view:
/********* Begin Procedure Script ************/
BEGIN
call "MISSIONCONTROL"."hpl.missioncontrol.procedures::get_mission_time_idle" (:IP_MISSIONID,IP_VEHICLEID,IP_PILOTID, lvSecondsIdle);
call "MISSIONCONTROL"."hpl.missioncontrol.procedures::get_mission_time_motion" (:IP_MISSIONID,IP_VEHICLEID,IP_PILOTID, lvSecondsMotion);
var_out = select label, seconds from :lvSecondsIdle
union all
select label, seconds from :lvSecondsMotion
union all
select 'PCTIDLE' as "LABEL", ABS(A.seconds / (A.seconds + B.seconds) * 100) from :lvSecondsIdle A
inner join :lvSecondsMotion B on A.IDX = B.IDX
union all
select 'PCTMOTION' as "LABEL", ABS(B.seconds / (A.seconds + B.seconds) * 100) from :lvSecondsIdle A
inner join :lvSecondsMotion B on A.IDX = B.IDX;
END /********* End Procedure Script ************/
where "var_out" for the two called procedures is:
type tt_seconds {
IDX : idxT;
LABEL : VSStringT;
SECONDS : secondsT;
};
And example from the "idle" procedure output
ex_idle_seconds = select 1 as "IDX", 'IDLE' AS "LABEL", lvTotalSecondsBetween as "SECONDS" from dummy;
Hi Patrick,
So far, I could successfully execute a procedure and could get the result from the result view to another table variable and could use it in CE function.
Unfortunately I haven't been able to use the OUT parameter value yet. Will have to look into the code for details.
I am not very positive about the performance impact of this, as the CE operators and the SQL code in procedure are executed by different engines and there could be high data transfer between the engines which would deteriorate the performance.
Regards,
Ravi
Add a comment