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

[marc_daniau]

Hi Dirk,

Here is an example of anonymous block running APL forecast without Declare.

DO BEGIN     
 header   = select * from #HEADER_APL;       
 config   = select * from #CONFIG_APL;    
 var_desc = select * from #DESCCRIPTION_APL;              
 var_role = select * from #ROLES_APL;  
      
 "SAP_PA_APL"."sap.pa.apl.base::FORECAST" ( 
 :header, :config, :var_desc, :var_role,
 'USER_APL','SERIES_IN', 'USER_APL','SERIES_OUT', 
 out_log, out_summary, out_indicators );
    
 select * from "USER_APL"."SERIES_OUT" order by 1;
END;

Cheers,

Marc