Can I call a procedure from a SQL Script calculation view

[patrickbachmann]

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

[sagarjoshi · Accepted solution]

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.

[patrickbachmann · Accepted solution]

Hi Sagar, is it possible for you to paste an example of the calc view code that you got to work that calls the external proc?

Thanks,

-Patrick

[patrickbachmann · Accepted solution]

PS:  I am not seeing any examples in the sqlscript guide that shows an SQL Script type calculation view that uses both these elements;

call MyProcedureName()

and then also

var_out = ResultsFromMyProcedure

[sagarjoshi · Accepted solution]

I am calling my procedure as follows and directly binding var_out

/********* Begin Procedure Script ************/

BEGIN

call "MYSCHEMA"."READ_PROC1" (:IV_MANDT,'E',var_out);

END /********* End Procedure Script ************/

Note: Procedure READ_PROC1 has a OUT table which has same structure as var_out.

[patrickbachmann · Accepted solution]

Excellent, let me try something similar now.  Thanks!

[patrickbachmann · Accepted solution]

Sagar, using your example code I was able to successfully call a procedure from my SQLScript calculation view!  Thank you so much for that.  Now I was wondering if you can answer this;  my procedure is doing a single thing;  it's simply doing a count of records based on my input variable called MyMovement:

PROCEDURE:

var_out = select count(*) as MyFirstCount from MyTableA where MoveType = :MyMovement;

VIEW SQL SCRIPT THAT WORKS:

call "_SYS_BIC"."myProcedure" ('122',var_out);

NOTE: var_out output table has a single output field

But now let's say I want my procedure to do TWO things.  So in my procedure I have a second similar statement that writes to a second output table called 'another_var_out'.

var_out = select count(*) as MyFirstCount from MyTableA where MoveType = :MyMovement; 

another_var_out = select count(*) as MySecondCount from MyTableB = :MyMovement; 

I can successfully validate and activate this procedure however I can't figure out the syntax to call it and get the two outputs.  ie: I tried this that failed;

call "_SYS_BIC"."myProcedure" ('122',var_out, another_var_out);

NOTE: This fails but in my var_out table I have TWO output fields in the var_out table. I am able to put multiple output tables in my procedure but I can't figure out how to create multiple output tables in my view that is calling the procedure NOR can I seem to write the two values to a single var_out.

[patrickbachmann · Accepted solution]

If I call directly in SQL editor I discovered I can call like this and get TWO result tables. 

call "_SYS_BIC"."myProcedure" ('122',?,?);

So while this is cool, obviously I think the problem with the Calculation View is it can't possibly display two result tables at the same time.  So really what I think I need to do is have the two select statements in the procedure write to the SAME single var_out table (two fields in var_out) but i have yet to figure out how to do that.

Have you had two separate select statements in a proc write to two fields in var_out table?

Thanks

[henrique_pinto · Accepted solution]

Does it also work with a dynamic value in the procedure call?

i.e. instead of '122', call it passing an input parameter that you create in the calc view and fill it during data preview.

[former_member182114 · Accepted solution]

Hi Patrick,

Taking your sample as sample.

call "_SYS_BIC"."myProcedure" ('122',var_out, another_var_out);

I'm guessing the 2 results of myProcedure are of same type. Isn't it?

If yes you just need to union these two results to fill var_out, like:

call "_SYS_BIC"."myProcedure" ('122',lt_result1, lt_result2);

var_out = CE_UNION_ALL(:lt_result1,:lt_result2);

or with SQL should be:

var_out = select MyFirstCount from :lt_result1 UNION ALL select MyFirstCount from :lt_result2;

In other hand if the output of your procedures are scalar variable, you first need to "transform" it on same table type for varout...

call "_SYS_BIC"."myProcedure" ('122',lv_var1, lv_var2);

var_out = select :lv_var1 as MyFirstCount from dummy union all select :lv_var2 as MyFirstCount from DUMMY;

Sorry, but as the sample is too generic the answer is also too much generic. I hope you understand what's behind.

Regards, Fernando Da Rós

[patrickbachmann · Accepted solution]

Actually before I started this test I was working with a CE_UNION_ALL just like you recommend.  However what I need to do is actually much more complicated than my example suggests.  I almost had everything working with CE_UNION however I'm having some divide by zero errors that I was hoping to fix with several passes.  I have to do a series of calculations.  I will experiment with your second suggestion though.  Thanks.

[former_member182114 · Accepted solution]

Hi Patrick,

The only difference of these two ways is table return or variable.

And this shouldn't solve your division by zero error.

Create a new thread with some more real sample so we can follow you.

Regards, Fernando Da Rós

[shishupalreddy · Accepted solution]

Hello Sagar ...It was a great help for me to figure out this .Thank you ...HANA experts Community

[former_member210482 · Accepted solution]

Hi Henrique,

Yes you can. See the below code

call "_SYS_BIC"."myProcedure" (:input_param,var_out);

You can even pass a table as input but the procedure has to be defined to accept a table.

[former_member197081 · Accepted solution]

This message was moderated.

[former_member258682]

This message was moderated.

[former_member182500]

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

[henrique_pinto]

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.

[sagarjoshi]

I have used passing input parameter from Calc view to called procedure and it worked. I think the problem you are mentioning is about passing parameters if there is nested call to another calc view.

[henrique_pinto]

Might be something that has changed on the latest versions. Functionally speaking, Calc Views are nothing more than Read-only procedures with Result Column view.

[patrickbachmann]

Guys thanks for all your responses.  See my question to Sagar now (above) about having multiple outputs.  Should I open a new topic for this since you really solved my first question?  Basically I want my procedure to do a few calculations (ie: execute various SQL that perform calculations) and then return several output variables to the CalcView.  I have figured out how to write a single SQL statement in a procedure that generates several output fields successfully to var_out but what if I want to do it with a second completely different SQL statement.  (ie: a second pass and send the outputs from both statements). 

Thanks!

PS: I'm waiting for Lars to chime in any minute now asking WHY do I do such things. 

[Former Member]

Hi Patrick,

I think I'm getting a bit lost as to what the actual question is, but nonethess, here's a bit of feedback. Hopefully it helps a bit!

1) Some folks' comments above is correct that there's currently not a way to cascade input parameters through from one procedure to another via SQL Script. Depending on use cases, this can be resolved in graphical calcviews, where input parameters do cascade.

2) As you noted, CalcViews only return one table output - so how to return multiple sets of data that you can distinguish is a good question. Of the data is roughly of the same structure, you could possibly fudge some fields, UNION ALL, and populate a flag column, which you can then later query against to split out the results. Could also do a join if the data sets are small enough to not kill performance. CE_VERTICAL_UNION is another option, though I have no experience with this.

If I understood your question correctly above though, about how to access multiple outputs from a stored proc inside of a CalcView, here is what I came up with. In order to make it work, I had to run GRANT EXECUTE ON SCHEMA <MY_SCHEMA> TO _SYS_REPO WITH GRANT OPTION on the schema where the stored proc is.

-- Code for stored proc

SET SCHEMA D055884;

DROP TYPE "STAT";

CREATE TYPE "STAT" AS TABLE ("COUNT" INTEGER);

DROP PROCEDURE "GET_COUNT";

CREATE PROCEDURE "GET_COUNT"(OUT var_out_1 "STAT", OUT var_out_2 "STAT")

READS SQL DATA AS

BEGIN     -- just some random code to populate two table variables (in this case one row each)

                var_out_1 = SELECT COUNT(*) AS "COUNT" FROM M_CS_TABLES;

                var_out_2 = SELECT COUNT(*) AS "COUNT" FROM M_RS_TABLES;

END;

-- Code for my CalcView - output table structure should be obvious

BEGIN

          CALL "D055884"."GET_COUNT"(a, b);

          var_out =

                    SELECT

                              'results from a' AS "MSG",

                              "COUNT"

                      FROM

                                :a

                      UNION ALL

                      SELECT

                                'results from b' AS "MSG",

                                "COUNT"

                      FROM

                                :b;

END

[patrickbachmann]

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.

[former_member184768]

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

[patrickbachmann]

Thanks for your feedback guys.  I will hopefully tinker some more with this today and update this with my findings in the next day.

[former_member184768]

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