CODEWITHSUNDEEP
databasehanarecursive-query
sridhar
sridhar

Reputation: 1157

How to implement recursion in HANA query

I believe that recursive function is not supported in HANA DB and hence, please suggest a HANA query to resolve the below problem.

Problem statement:

Considering the below table, as one input available in hand 'orange' for querying the below table to get values of 'child' of 'orange' and at the same time, obtained 'child' values should be treated as 'parent' to fetch all the relevant child values.

Table: (assume table name as 'fruits')

child   parent

apple   orange
grapes  apple
pomo    grapes
pears   orange
plums   pears
jack    cashew

Input: 

column name-parent, value-orange

Expected ouput:

apple, grapes, pomo, pears, plums

Upvotes: 1

Views: 8100

Answers (2)

Dan Markman
Dan Markman

Reputation: 1

Just in case HANA version is at 1.0 there's an alternative solution with recursive column views generated by calculation views with a hierarchy set up here. This view has a name of a calculation view + "\name_of_hierarchy" and is placed in _SYS_BIC schema in column views.

You can use MDX enhancements for SQL to work with recursive/hierarchy logic like: select * from "_SYS_BIC"."column view/hierarchy_view" ("expression" => 'descendants(*)')

The syntax is wast and depends on the requirement.

Upvotes: 0

Lars Br.
Lars Br.

Reputation: 10388

SAP HANA does support Recursive SQLScript Logic starting with HANA 2 SP04. So, you could implement your query in a recursive way using recursive stored procedures.

Given your requirement, you might be better off looking at what SAP HANA provides instead of WITH RECURSIVE Common Table Expressions: A Hierarchy Data Model.

With it, you can define your hierarchy (this is basically what you described in your question) and then ask many different questions, e.g. "what are all fruits below grapes?" or "what fruits have 'apple' as the direct parent?" and many others. This works without any expensive recursion and has been available in HANA now for several years.

Another benefit of this approach is that the hierarchy structure is explicitly defined and not implicitly assumed by the way you traverse the data. In my view, this makes it easier to separate the thinking of how the hierarchy is constructed and of the question you like to answer.

The only downsides I see to this is that these features are of course proprietary and cannot be used 1:1 on other DBMS. Also, if you've never worked with hierarchical data in an RDBMS before and only try to copy solutions you found somewhere, then this will require some own thinking/learning on your end; that is to say, there is a learning curve.

Upvotes: 2

Related Questions