cancel
Showing results for 
Search instead for 
Did you mean: 

Calculate using a previous rows calculated value on the fly in SAP HANA

former_member337435
Participant
0 Kudos

Hi ,

I need to calculate the current day's quantity by using the previous day's quantity. The previous day's quantity is also calculated as shown in the example table below.

How do I do this calculation in a graphical calculation view or a table function.

I cannot use SUM over because when the value is negative, I need to set it to zero and then use that zero. I am not sure how to use LAG since its a calculated value on the fly. Without using arrays or cursors, how can I accomplish this?

Quantity31-Mar1-Apr2-Apr3-Apr4-AprQty110060-5-14-10Qty2206080620Qty36012589267Calc- Qty460-5-14-10-57Calc-Qty4-no negative600040

The first day 31-Mar calc-Qty4-no negative is calculated value and I have no issue for the first day.

For the second day 1-Apr, calc-Qty4-no negative is calculated as 31-Mar calc-Qty4-no negative + 1-Apr Qty2 - 1Apr Qty3. If this value is negative, then it should be a 0. THis 0 should be used for the next days calculation.

Similarly the calculations need to go on for the remaining days.

Thanks for your help in advance!

former_member337435
Participant
0 Kudos

Thanks a lot Lars for your detailed and informative answer ! Did learn a lot from your answer. I did try the NO_CALC_VIEW_UNFOLDING with value =1 in the calculation view that calls the table function. The CV is still not getting activated and its giving the same error. I did also try

NO_CALC_VIEW_UNFOLDING(Name of the table function) with value =1 and still getting the same error. I did try the synonym ceq20_disable_unfolding with value as 1 but still no luck.The Calculation view that is called inside the table function is a bunch of unions of different calculation views.I don't think that matters but still letting you know.

lbreddemann
Active Contributor

The syntax for the hint is

select ... from <calc view>
with hint (NO_CALC_VIEW_UNFOLDING)


which is to say, that the hint is placed in the SQL that references the calc view.

If you want to put it in the calc view, then you can create a wrapper table function in which you just call the table function with the hint.

former_member337435
Participant

Thanks Lars. I did create a wrapper table function and included the other table function with the HINT and it worked.

former_member701033
Discoverer
0 Kudos

Hi All,

Any idea how to calculate the result qty each time when transaction is being done for the below?

e.g. BAR-COR-31 has always the QTY in stock the same as there is no result qty field available on system. I need to calculate the result qty based on the result qty of the previous transaction of the same Item.

so for BAR-COR-31 with transaction 3 it would be 8145 as next transaction was +15 which gives us 8160-15=8145.

Thank you in advance.

former_member701033
Discoverer
0 Kudos

Adding the attachement as it

hasn't uploaded.

Accepted Solutions (1)

Accepted Solutions (1)

pfefferf
Active Contributor
0 Kudos

As in the calculation of each line a calculated value of the previous line is necessary, it is not possible to solve that using window functions like e.g. LAG.

I would go with a table function doing a simple loop over the data sorted according to your requirements. Within the loop I would use Index-Based Cell Access for an efficient access of the data required for the calculation.

former_member337435
Participant
0 Kudos

Thanks Mr.Florian. I am working on looping through the data in a table function. But the calculation view inside the table function has multiple input parameters and three of them can take multiple values. SQLSCRIPT library function is not working.

former_member337435
Participant
0 Kudos

Hi Florian,

I tried to use index based Cell access as you suggested . But can you use this process inside a Table function.

:T1 is a calculation view that retrieves data and I is the index. This works perfectly fine and is performance efficient as well when run in the SQL console. But when I copy this inside a Table function, activation gives the following error.

Modification of table variable is not supported in current SQLScript configuration

FOR I IN 1 .. RECORD_COUNT(:T1 ) DO
IF :T1.DATE1[I] = :T1.HSTDT[I] THEN

IF (:T1."BI"[I] + :T1."TS"[I] - :T1."TD"[I]) > 0 THEN

T1.(CALCVALUE)[I] = (:T1."BI"[I] + :T1."TS"[I] - :T1."TD"[I] + :T1."TS"[I-1] - :T1."TD"[I-1] );
ELSE
:T1.(SS).UPDATE((:T1."BI"[I] + :T1."TS"[I] - :T1."TD"[I]),I) ;
ENDIF;
ELSEIF (:T1.DATE1[I] > :T1.HSTDT[I]) THEN
IF(:T1."CALCVALUE"[I-1] + :T1."TS"[I] - :T1."TD"[I]) > 0 THEN
:T1.(CALCVALUE).UPDATE((:T1."CALCVALUE"[I-1] + :T1."TS"[I] - :T1."TD"[I]),I) ;

ELSE

:T1.("SS").UPDATE((:T1."CALCVALUE"[I-1] + :T1."TS"[I] - :T1."TD"[I]),I) ;
ENDIF;
ENDFOR;

Without this if I do the calculation view using the table function is taking a lot of time. Kindly requesting your help.

pfefferf
Active Contributor
0 Kudos

lalitha16 Can you please show your function header definition; and what HANA version you are using?

former_member337435
Participant
0 Kudos

Hi Florian,

Please find the function definition below. I have removed multiple output fields for easy understanding.

Hana version is

HANA 2.0 SPS 03 rev 37

.

FUNCTION "NASCHEMA"."TEST1::TF_TEST1" (
IPDBLENGTH INTEGER,
IPWBLENGTH INTEGER,
PCF INTEGER,
IPMAT1 VARCHAR(5000),
IPMAT2 VARCHAR(5000),
IPMAT3 VARCHAR(5000)
)
RETURNS TABLE
(
"MATNR" NVARCHAR(18),
"PWWRK" NVARCHAR(20),
"TS" DECIMAL(18,5),
"TD" DECIMAL(18,5),
"B1" DECIMAL(18,5),
"CALCVALUE" DECIMAL(18,5),
"SS" DECIMAL(18,5)
)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
AS
BEGIN

Thanks for your time and help !
pfefferf
Active Contributor
0 Kudos

Hm, just tested it on an "old" HANA 1.0 SPS12 release without any problems (except the UPDATE function which is not available on that release). But according to the error message it seems that a configuration does avoid the activation; and in general it is supported as you said it works in the console (in an anonymous block I think). Are you creating that stuff via the HANA studio; if yes did you try to create it via the Web-based dev workbench? Can you check to which value the "sqlscript_mode" configuration parameter is set (via system view M_INIFILE_CONTENTS for instance)?

former_member337435
Participant
0 Kudos

Sure will check the SQL script configuration . I am thinking does it give an error because it has the update of a table variable since the error indicates modification of table variable is not supported . In the SQL console I did try through the anonymous block.i have not used web based workbench.

Answers (1)

Answers (1)

former_member337435
Participant
0 Kudos

Ok another update is the table function is getting activated with errors. But during this table function activation , the calculation view that calls this table function in a projection node is failing with the error.

Modification of table variable is not supported in current SQLScript configuration

I was also able to do a select on the table function. But I need to call it in the calculation view.Can you please tell me if anything needs to be changed on the calculation view side or configuration side.

lbreddemann
Active Contributor

-- ok, I just put this into a blog post for easier reference.

Yes, this error message - sadly under-documented - is a by-product of the unfolding/unrolling efforts of the HANA query optimizer when dealing with calculation views (also see https://blogs.sap.com/2019/08/29/when-to-use-execute-in-sql-engine-for-calculation-views/ and SAP note https://launchpad.support.sap.com/#/notes/1857202 for some background on this).

Basically, when HANA tries to convert the table function into an SQL equivalent during the view unfolding, it realizes that array manipulation or direct result set manipulation doesn't have SQL equivalents. The usual reaction to this would be to simply not do the un-folding and not the SQL engine for this part of the query - but HANA 2 seems to have some rough edges, where it just spits out the error message instead.

There even is a related SAP note #2857606- Error "MAP_MERGE operator is not supported in current SQLScript configuration when running a calculation view that explains the error in relation to the MAP_MERGE operator.

The base problem, however, seems to be the same as with the array manipulation (which, by the way, does not throw the error in HANA 2 SPS 04 anymore!)

Now, what can you do about this?

One workaround is described in the mentioned SAP note: use the NO_CALC_VIEW_UNFOLDING() hint.

Worthwhile to remember (or learn) here is that this hint takes the names of the calc-view (or table functions) where the unfolding should be avoided as parameters. That means, with the hint

WITH HINT( NO_CALC_VIEW_UNFOLDING(<the name of the fancy table function>) )

the hint is restricted to the table function and not to the whole calc. view.

The main problem with this approach is the same that all hint-based-solutions have:

You just bought yourself a maintenance issue.

Anyhow, that's what it is at the moment.

Cheers,

Lars