title: SAP Hana - SQL - calculate columns on the same table without recursive functions
body: <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>