Skip to Content
0

How to retrieve hierarchical data in SAP HANA?

Jan 09, 2017 at 02:10 PM

106

avatar image
Former Member

sample.pngHello,

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

https://answers.sap.com/questions/78877/error-executing-query-on-hana-database.html

(3) Cross Apply.

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

Thanks

sample.png (10.5 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

0 Answers