PAL Error while invoking from a stored proc[HANA Solution Adoption and Deployment (CSA)]

[Former Member]

Hello All,

I'm facing a strange error.

When i invoke a Triple Exponential Smoothing Algorithm  from SQL Console,

Everything works fine as expected and i get the desired results.

The input data and parameters have been prepared as explained in the documentation.

CALL _SYS_AFL.WOW_FCST_TRIPLE (V_WOW_FCST_DATA, WOW_FCST_PARAMS, WOW_FCST_RESULTS) WITH OVERVIEW

The problem arises when i try to invoke the same PAL Algorithm procedure from a SQL Script procedure.

Input data, parameter and parameters are same, and I triple checked for errors with this.

Procedure is created without any problem , but when i run it i get the error :

Could not execute 'call getTesOutputNew('2010-01-05')' in 196 ms 773 µs .

SAP DBTech JDBC: [2048]: column store error: search table error:  [2620] VINAY.GETTESOUTPUTNEW: line 5 col 2 (at pos 97): InternalFatal exception: _SYS_AFL.AFLPAL:TRIPLESMOOTH: AFLFunctionFatal exception: PAL error[73001043]:Value [7] of parameter CYCLE is invalid. $APPEND$

What is strange is that , when i run the algorithm in SQL Console, i use the same parameters and it works fine.

Below is the snippet for create procedure : , i have checked for authorization,syntax and other issues and everything is what its supposed to be.

PFA the indexserver trace.

Any pointers on this?

drop procedure getTesOutputNew;

CREATE PROCEDURE getTesOutputNew( IN TES_TILL_DATE DATE)

     LANGUAGE SQLSCRIPT AS

BEGIN

  inputtable = select

  row_number() over (order by "DATE" ) as CALENDAR_DATE,

  PROD_QTY

  from VINAY.ITEMSALESUBSET

  where "DATE"

  between '2010-01-01' and :TES_TILL_DATE

  order by

  CALENDAR_DATE ;

P_WOW_FCST_PARAMS = select * from VINAY.WOW_FCST_PARAMS;

CALL _SYS_AFL.WOW_FCST_TRIPLE (:inputtable,:P_WOW_FCST_PARAMS, P_WOW_FCST_RESULTS) WITH OVERVIEW;

SELECT * FROM :P_WOW_FCST_RESULTS;

--insert into  VINAY.FCST_HIST

--select top 1 *,to_DATE('2010-01-01'),'140574' AS "ARTICLE",'1282' AS "STORE"  from :P_WOW_FCST_RESULTS order by CALENDAR_DATE DESC;

[freekmenger]

Hi Vinay,

I just ran into the same error, but this time with the DOUBLESMOOTH PAL function. With doublesmooth you cannot enter the parameter CYCLE into the control table, however it is indicating that the CYCLE parameter is 2. With triplesmooth you can change the CYCLE parameter in the control table, but the error message will still indicate what number you gave (in your case: 7).

The problem (in my case) was caused by the fact that the data input table (in your case the :inputtable that is selecting data from VINAY.ITEMSALESUBSET) does not contain enough values. You need to have at least the amount of values in your data input table as the parameter CYCLE holds, so 7 records.

Good luck!

[rindia]

Hi Vinay,

In PAL algorithm (Triple Exponential Smoothing), the pre-requisites are

1. No missing or null data in inputs
2. The data is numeric
3. The procedure name is same as specified in procedure generation.

        CALL SYSTEM.AFL_WRAPPER_GENERATOR('<procedure name>','AFLPAL','TRIPLESMOOTH', <signature table>);

As your procedure name is WOW_FCST_TRIPLE then procedure generation should be

        CALL SYSTEM.AFL_WRAPPER_GENERATOR('WOW_FCST_TRIPLE','AFLPAL','TRIPLESMOOTH', <signature table>);

Also from your error, which says, parameter CYCLE is invalid.

Please check the <signature table> 5th parameter of parameter table (P_WOW_FCST_PARAMS) which is CYCLE the value has to be > 1 and by default it is 2. What is the value it has?

Actually I do not have privilege to call AFL procedure and could not test your scenario

Regards

Raj Kumar Salla