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 Member · 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 Member · 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 Member · 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.