HANA Hierarchy没有提供sys_connect_by_path的功能,只有通过程获取:
DO
BEGIN
allhr =
select HIERARCHY_RANK id, HIERARCHY_PARENT_RANK pid, parent_id item, node_id node from (SELECT HIERARCHY_RANK,HIERARCHY_TREE_SIZE,HIERARCHY_PARENT_RANK,HIERARCHY_ROOT_RANK,HIERARCHY_LEVEL,HIERARCHY_IS_CYCLE,HIERARCHY_IS_ORPHAN,PARENT_ID,NODE_ID
FROM hierarchy (
SOURCE (SELECT name parent_id,idnrk node_id FROM pcdw.ecc_bom a WHERE WERKS ='L070')
START WHERE name IN ('7D04CTO1WW','7X18CTO1WW')
));
curpath = select id,pid,item,node,item root,item||','||node path from :allhr a where pid=0;
while (select count(*) from :curpath where id is not null) >0 do
curpath = select b.id,b.pid,b.item,b.node,a.root,ifnull(a.path||','||b.node,a.path) as path
from :curpath a left join :allhr b on a.id=b.pid and a.node = b.item;
end while;
select root,path from :curpath;
END;
找到个新方法:
WITH t_demo AS (
SELECT '1' p,'1.1' c FROM dummy UNION
SELECT '1' p,'1.2' c FROM dummy UNION
SELECT '1' p,'1.3' c FROM dummy UNION
SELECT '1.2' p,'1.2.1' c FROM dummy UNION
SELECT '2' p,'2.1' c FROM dummy UNION
SELECT '2' p,'2.2' c FROM dummy UNION
SELECT '2' p,'2.3' c FROM dummy UNION
SELECT '2.1' p,'2.1.1' c FROM dummy
)
, h AS ( SELECT * FROM HIERARCHY (
SOURCE ( SELECT c node_id, p parent_id FROM t_demo ORDER BY c )
START WHERE p = '2' ) )
SELECT
start_id AS node_id,
STRING_AGG(node_id, '/' ORDER BY hierarchy_rank) AS path
FROM
HIERARCHY_ANCESTORS(
SOURCE h
START ( SELECT parent_id AS start_id, hierarchy_rank AS start_rank FROM h )
)
GROUP BY
start_id;