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

[marc_daniau]

Here is the code to prepare the tables:

create view SERIES_IN as select * from APL_SAMPLES.OZONE_RATE_LA order by "Date" asc;

create table SERIES_OUT (
	"Date" DAYDATE,
	"OzoneRateLA" DOUBLE,
	"kts_1" DOUBLE,
	"kts_1_lowerlimit_95%" DOUBLE,
    "kts_1_upperlimit_95%" DOUBLE
);

create local temporary table #HEADER_APL       like "SAP_PA_APL"."sap.pa.apl.base::BASE.T.FUNCTION_HEADER";
create local temporary table #CONFIG_APL       like "SAP_PA_APL"."sap.pa.apl.base::BASE.T.OPERATION_CONFIG_EXTENDED";
create local temporary table #DESCCRIPTION_APL like "SAP_PA_APL"."sap.pa.apl.base::BASE.T.VARIABLE_DESC_OID";
create local temporary table #ROLES_APL        like "SAP_PA_APL"."sap.pa.apl.base::BASE.T.VARIABLE_ROLES_WITH_COMPOSITES_OID";
create local temporary table #LOG_APL          like "SAP_PA_APL"."sap.pa.apl.base::BASE.T.OPERATION_LOG";
create local temporary table #SUMMARY_APL      like "SAP_PA_APL"."sap.pa.apl.base::BASE.T.SUMMARY";
create local temporary table #INDICATORS_APL   like "SAP_PA_APL"."sap.pa.apl.base::BASE.T.INDICATORS";

insert into #HEADER_APL values ('Oid', 'My Forecast');
insert into #CONFIG_APL values ('APL/Horizon', '3',null);
insert into #CONFIG_APL values ('APL/TimePointColumnName', 'Date',null);
insert into #CONFIG_APL values ('APL/ForcePositiveForecast', 'true',null);
insert into #CONFIG_APL values ('APL/ApplyExtraMode','Forecasts and Error Bars', null);
insert into #ROLES_APL  values ('Date', 'input',NULL,NULL,NULL);

Marc

[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

[dirk_meyer2]

Hi, 

Thanks for the response.
With the code exaples inthe latest APL Version it is indeed solved different.

Problem is that I can not find any sources on Scripting that get a bit deeper into the consequences of Do Begin as if this is a standard behaviour, how to set it strategically (found out that you can do it multiple time in a sequnce) and what the advantages are.
The books and how tos in know only do that on a "Hello World" level.

Python is and also not that of an option since.
Installation of ML is blocked by security and starting to deal with opening ports is mor dreadful than dealing with Scripting which some competence is handy too.