Can I call a procedure from a SQL Script calculation view

[#1]

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

[Former Member]

@ Jody Hesch

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;

[Former Member]

Good stuff!!