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";

## Add comment