Heirarchy function in hana sql

[former_member614035]

Heirarchy function returns empty set, though data is present in source.

SELECT * FROM HIERARCHY( SOURCE (SELECT "TEAM_ID" as node_id,"REPORTSID" as parent_id,"TEAM_NAME" FROM "abc.TeamStructure") START WHERE "REPORTSID" IS NULL )

Please let me know, is ther any problem in sql.How can i achieveheirarchical functionality.

[mfath]

Are you using WebIDE? Beside the result set being empty - do you see any error message?

Can you please add a SIBLING ORDER BY clause as described in

hierarchy generation

[mfath · Accepted solution]

Hej Chanikya,

can you please try to run the inner/source query?

SELECT "TEAM_ID" as node_id,"REPORTSID" as parent_id,"TEAM_NAME" FROM "abc.TeamStructure" WHERE "REPORTSID" IS NULL

if this query returns a non-empty result, then the hierarchy result should also be non-empty.

In case you are not aware, the hierarchy developer guide provide some real nice examples.

Regards, Markus

[former_member614035 · Accepted solution]

Hi Fath,

Thanks for the reply. Yes the inner query

"SELECT "TEAM_ID" as node_id,"REPORTSID" as parent_id,"TEAM_NAME" FROM "abc.TeamStructure"

returns result set.

But still the hierarchy result is empty.

[former_member614035 · Accepted solution]

Hi Markus,

Yes I am using WEB IDE, and there are no error messages. The query executed successfully but with empty result set.

And also added SIBLING ORDER BY, but there is no use. Still the situation is same.

[former_member614035 · Accepted solution]

Hi Markus,

Thanks for the quick response. The issue is resolved as the root value of parent id value is string in null. So the heirarchy function not able to find which is the root element. That's why i am getting an empty result set.