on 03-31-2020 6:54 AM
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!
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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
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)?
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.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
-- 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
User | Count |
---|---|
87 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.