Skip to Content
avatar image
Former Member

Recursive subquery in HANA

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Feb 20, 2017 at 11:00 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded