Dependent Calculation in SAP HANA SQL

[Former Member]

Hello,

we are trying to optimize a transformation and pushing the code to SAP HANA as an SQL routine.

We have a table with columns a,b,c,d,e,f, g a and b are the keys and c - g are keyfigures. In some cases all key figures are prefilled and in other cases we need to calculate them.

For example from source:

key1; key2; 3;5;30;6;180

key1b;key2b;4;6;0;0;0

So if column

e is 0 it should be calculated as c*10

f is 0 it should be calculated as e / d

g is 0 it should be e * f

Should be

key1; key2; 3;5;30;6;180

key1b;key2b;4;6;40;6.66;266.4

So we have a strict order in which the columns have to be calculated for not messing up the dependencies, in ABAP its easy with a for loop. But in SQL if we try to use aliases for the first calculation and reuse it in the next it says unknown name and the normal calculation in the SQL select does not use the formerly calculated values. The number of reused results can be 5 or even more. So to calculate key figure 20 we need 18, which needs 15, which needs 14, which needs 13 etc.

Is there a way to achieve this without using cursours or arrays as temp table?

To clear things up, this is the idea:

select 
[...]
("/BIC/AWE_A" + 5.32) as newe,
("/BIC/WEA_A" * newe) as newres,
("/BIC/AWE_P" / newres) as final
 from "tablea";
 
 select 
[...]
("/BIC/AWE_A" + 5.32) as "/BIC/AWE_A",
("/BIC/WEA_A" * "/BIC/AWE_A" [new value]) as "/BIC/WEA_A",
("/BIC/AWE_P" / "/BIC/WEA_A" [new value]) as "/BIC/AWE_P"
 from "tablea";

[lbreddemann · Accepted solution]

Sure there is.

A sequential calculation is very easy to accomplish in SQL: via nesting the select statements.

select col_a_calc1 as col_a_out, 
       col_b + col_a_calc1 as col_b_out,
       col_c - col_b as col_c_out
from 
    (select col_a * 0.53 as col_a_calc1, col_b, col_c 
     from (select col_a, col_b, col_c
           from table)
     );
 

and so forth.

In calculation view "logic" this would be stacked calc views.

If you want to use SQLScript for that, you can do so and make your life easier by assigning each calculation level to a separate table variable. This table variable then is used as the source for the next calculation. Very easy to do and very easy to organise.