Skip to Content

Can I call a procedure from a SQL Script calculation view

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

6 Answers

  • Best Answer
    Posted on Feb 18, 2013 at 03:43 AM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Feb 17, 2013 at 07:21 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Feb 18, 2013 at 05:10 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

    • Jody thanks for your input. Actually the solution I finally have working is very similar so it was helpful. I have several table vars. TableVar1 runs some sql. TableVar2 runs some sql. TableVar3 does a select on the first two vars (this I didn't realize I could do initially and was a life saver). TableVar4 does more processing of tableVar3 and then I CE_JOIN two of them and it seems to be working. And I'm sure I've confused everyone by now. Haha. I'm sure I will have more questions but closing this discussion and will start a new one if I do.

      Thanks everyone.

  • Posted on Apr 11, 2014 at 04:37 PM

    @ 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;

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Feb 18, 2013 at 08:33 AM

    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
    10|10000 characters needed characters exceeded

  • Posted on Jun 28, 2016 at 01:36 PM

    This message was moderated.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.