Error with SQLScript Hierarchy Function on SAP HANA Database
SAP enables HANA database developers to query parent-child or hierarchy data structures using SQL Hierarchy function
When I execute SQLScript sample about SAP HANA Hierarchy function shared at SAP HANA SQL and System Views Reference document, I got following error
Could not execute 'SELECT hierarchy_rank AS "rank", hierarchy_tree_size AS "tree_size", hierarchy_parent_rank AS ...'
SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near "SELECT":
Although it is not stated at reference documentation, SQLScript Hierarchy function is not supported on HANA database versions prior to 2.00.022
Here is the sample SQL code taken from Hierarchy function reference documentation published by SAP
SELECT
hierarchy_rank AS "rank",
hierarchy_tree_size AS "tree_size",
hierarchy_parent_rank AS "parent_rank",
hierarchy_level AS "level",
hierarchy_is_cycle AS "is_cycle",
hierarchy_is_orphan AS "is_orphan",
node_id,
parent_id,
type
FROM HIERARCHY (
SOURCE
(
SELECT
id AS node_id,
parent AS parent_id,
type
FROM Hierarchy
ORDER BY ord
)
CACHE FORCE
)
ORDER BY hierarchy_rank;
If you are not running your SAP S/4HANA system on a HANA database 2.0, SQL programmers will experience SQL syntax error.
On the other hand, if you are running your SAP HANA system on a HANA database version 2.00.022.00 for example, you will get following output successfully querying the parent-child relation also known as hierarchy data.
If you have installed a HANA database version which is supporting Hierarchy function usage on hierarchical data, you can refer to tutorial Query Hierarcy Data in SAP HANA Database using SQLScript for samples.
