SAP Hana - SQL - calculate columns on the same table without recursive functions
<p>I have the following simple table with approx. 40k items:</p>

<pre><code>|date|consum|
</code></pre>

<p>Based on this table, I want to calculate some new columns. At the end, the table should looks like:</p>

<pre><code>|date|consum|row|row_relativ|min|max|upper|lower|
</code></pre>

<p>Unfortunately the SAP Hana Database does not support recursive table statements... </p>

<p>Here are the calculation rules:</p>

<ol>
<li>row = <code>row_number() over(order by dates)</code></li>
<li>row_relative = <code>row*35040/32768</code></li>
<li>min = <code>floor(row_relative)</code></li>
<li>max = <code>ceil(row_relative)</code></li>
<li>min = select the consum from the row with the min value</li>
<li>max = select the consum from the row with the max value</li>
</ol>

<p>At the moment I have a stored precedure with 5 variables. In each variable I calculate i new coulmn. It is very very dirty... How can i make it different? I cant use recursive functions... :/ </p>

<p>Here is my sql snippet... have fun =)</p>

<pre><code>var1 = select *, row_number() over(order by dates) as row from XYZ order by dates limit 34944;
var2 = select *, (row * 34944/32768) as row_relative from :var1;
var3 = select *, floor(row_relative) as min, ceil(row_relative) as max from :var2;

var4 = select 
    p.*,
    (select consumfrom :var3 where row = p.min) as lower,
    (select consumfrom :var3 where row = p.max) as upper
from :var3 p;

var5 = select 
    p.*,
    (p.lower* (1-p.row_relative+p.min)+p.upper * (p.row_relativ - p.min)) as new_consum
from :var4 p;
</code></pre>