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

[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.