Question Re: Can I call a procedure from a SQL Script calculation view in Technology Q&A

Can I call a procedure from a SQL Script calculation view

patrickbachmann — Sun, 17 Feb 2013 18:56:52 GMT

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

Re: Can I call a procedure from a SQL Script calculation view

Ravi_Channe — Sun, 17 Feb 2013 19:21:37 GMT

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

Re: Can I call a procedure from a SQL Script calculation view

sagarjoshi — Mon, 18 Feb 2013 03:43:41 GMT

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.

Re: Can I call a procedure from a SQL Script calculation view

Ravi_Channe — Mon, 18 Feb 2013 08:33:06 GMT

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

Re: Can I call a procedure from a SQL Script calculation view

patrickbachmann — Mon, 18 Feb 2013 14:45:13 GMT

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

Re: Can I call a procedure from a SQL Script calculation view

henrique_pinto — Mon, 18 Feb 2013 17:10:48 GMT

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.

Re: Can I call a procedure from a SQL Script calculation view

sagarjoshi — Tue, 19 Feb 2013 03:39:52 GMT

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.

Re: Can I call a procedure from a SQL Script calculation view

henrique_pinto — Tue, 19 Feb 2013 13:36:33 GMT

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.

Re: Can I call a procedure from a SQL Script calculation view

patrickbachmann — Tue, 19 Feb 2013 15:59:14 GMT

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

Re: Can I call a procedure from a SQL Script calculation view

patrickbachmann — Tue, 19 Feb 2013 16:02:34 GMT

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

Re: Can I call a procedure from a SQL Script calculation view

sagarjoshi — Tue, 19 Feb 2013 16:33:05 GMT

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.

Re: Can I call a procedure from a SQL Script calculation view

patrickbachmann — Tue, 19 Feb 2013 16:36:05 GMT

Excellent, let me try something similar now. Thanks!

Re: Can I call a procedure from a SQL Script calculation view

patrickbachmann — Tue, 19 Feb 2013 21:08:28 GMT

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.

Re: Can I call a procedure from a SQL Script calculation view

patrickbachmann — Tue, 19 Feb 2013 21:19:53 GMT

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.

Re: Can I call a procedure from a SQL Script calculation view

patrickbachmann — Tue, 19 Feb 2013 21:56:41 GMT

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

Re: Can I call a procedure from a SQL Script calculation view

henrique_pinto — Tue, 19 Feb 2013 22:44:51 GMT

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.

Re: Can I call a procedure from a SQL Script calculation view

Former Member — Tue, 19 Feb 2013 23:54:29 GMT

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

Re: Can I call a procedure from a SQL Script calculation view

patrickbachmann — Wed, 20 Feb 2013 13:27:01 GMT

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.

Re: Can I call a procedure from a SQL Script calculation view

Former Member — Wed, 20 Feb 2013 17:30:36 GMT

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

Re: Can I call a procedure from a SQL Script calculation view

Former Member — Wed, 20 Feb 2013 18:43:38 GMT

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

UNION ALL

SELECT

'results from b' AS "MSG",

"COUNT"

FROM

:b;

END