Using PAL Procedures embeded in SQL Script (anonymous blocks)

[dirk_meyer2]

Hi all,
I stumbled over a change in behaviour of PAL Procedures after i embedded them in an anonymous srip blog.
My idea is to make certain procedure with dynamic programing easier to us/reuse.

As an exampel serves PAL_MULTIVARIATE_ANALYSIS
In the documentation example it is fed with local temporary table and the result table can be defined relativly flexibe. The column names do not have to be identical with the input cols.

As soon as I use "Do Begin - End" the behaviour completely changes.
This seems necessary for usage of DECLARE.
Only table variables are accepted in and out. The naming of outtab has to be exactly like the intab (plus the first col).  With that necessity the hole thing gets more complicated in order to declare tabs dynamicly.
With APL I think its the same drama.

Why is that so and is there a propper not tinker work arround. 
Maybe its just my limited experience with SQL sripting and as so as it is again stored as procedure its all diffenrent again.

Very gratefull for all helpful clues.
Dirk

[ChristophMorgen · Accepted solution]

Hi Dirk,

based on your PAL procedure example, here is a simple example using anonymous blocks, you don't have to declare the structure of the PAL procedure output:

do
begin
tv_data = SELECT * FROM PAL_MULTIVARSTAT_DATA_TBL;
tv_parms = SELECT * FROM #PAL_PARAMETER_TBL;
CALL "_SYS_AFL"."PAL_MULTIVARIATE_ANALYSIS"(:tv_data, :tv_parms, tv_out);
SELECT * FROM :tv_out;
END;

The constraint if structures require to be declared or not comes from SQL Script.

Please note, usage of the Python Machine Learning client might be helpful, as you can script in Python and capture the ad-hoc generated SQL for the PAL procedure or even utilize methods to generate procedure code / even design-time artifacts. Just as another helping note.

I hope this is helpful, otherwise please share more details.

Best regards,

Christoph