Recursive subquery in HANA

[former_member259926]

Hi All

I am trying to use recursive subquery option in HANA , but when I referred to sql reference document, it says that recursive query for WITH clause is not supported as of 2.0.

The business use case I am trying to achieve is as below. I am trying to achieve this without loops (or) cursors. Please share your thoughts.

Product   cost    percent   expected_result
  1         10       2         20
  2          0       3         20 + (20 * 3) = 80
  3          0       4         80 + (80 *4) = 400
  4          0       5         400 + (400 * 5) = 2400

Thanks & Regards

Santosh Varada

[lbreddemann]

When don’t you use the hierarchy functions that HANA provides for this?

[lbreddemann]

So, you want a running product here, instead of a running total, right?

That's doable in SQL, but it's not pretty:

https://stackoverflow.com/questions/3912204/why-is-there-no-product-aggregate-function-in-sql .

Basically, the idea is to use log() and SUM() to build the product based on the summation of log() for non-negative numbers. As it's easy to get this wrong, you might be better of by writing a function instead.