Skip to Content
avatar image
Former Member

How to retrieve hierarchical data in SAP HANA?


In SAP HANA, we want to perform the hierarchy based selection which will include the child records as well. We are able to achieve this in SQL server database by creating a database function which gives all the hierarchy nodes (used CTE recurrence query and cross apply).

We are looking for the equivalent SAP HANA Query to achieve this. Below are the some details about the database structure and our requirements.

(1) We store the hierarchy relation into database as per table #1 in the attachment 'Sample.png'

(2) The relation between the records and the child nodes is stored as per table #2 in the attachment 'Sample.png'

The column 'Include Child' indicates that it will be applicable to all its child records when set to 1. If set to 0 it will be applicable up to that node only.

I need to perform select on these node in such a way that if include child of node is 1 then it selects all its children’s including sub children’s available at all level along with node. If include child of node is 0 then its simply selects that node only.

In above example my expected result is result is A,A1,A2,A11,A12,A21,B . Refer table #3 in the attachment 'Sample.png'.

Due to the limitations of HANA, it does not support the following:

(1) Recurrence SQL (CTE)

(2) Passing the Dynamic value to Table Value Function Example: SELECT COL1 FROM TBL1 T1 CROSS JOIN FN_ABC(T1.ID,100000) FN

(3) Cross Apply.

Could you please let us know if there is any way to achieve this in HANA?


sample.png (10.5 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

0 Answers