2
0
Recently one of my friend asked ,how to implement the CONNECT_BY_ISLEAF in Snowflake. CONNECT_BY_ISLEAF is a pseudocolumn that returns a 1 if the row is a leaf in the hierarchy as defined by the CONNECT BY clause. A node is a leaf node if it has no children in the query result hierarchy . If the row is not a leaf the column returns 0 .
Consider the below diagram, Box marked in brown are the leaf nodes. These nodes don’t have any child associated with them, The boxes marked in the Blue called to be parent or grand parent. So as per the property, Brown box should return 1 value while the Blue boxes should represent 0
So when you run the CTE using, CONNECT_BY_ISLEAF inside the snowflake, we are getting following error.
While if I run the same query inside the ORACLE ,the query gets completed successfully and return the out. Please see the below screenshot from the Oracle:
So question comes how to implement the same in Snowflake. After analyzing the multiple blogs on Google with respect to the Oracle,I could able to compile the below query in snowflake. This query returns the same output we have obtained in ORACLE.
with emp_mgr_hierarchy (lvl, ename, empno, mgr,connect_By_is_leaf) as (
select 1 lvl, ename, empno, mgr,0 connect_By_is_leaf from emp m where mgr is null
union all
select h.lvl + 1, e.ename, e.empno, e.mgr, (select (case when count(*) = 0 then 1 else 0 end) from emp a where a.mgr = e.empno) connect_By_is_leaf
from emp_mgr_hierarchy h, emp e where e.mgr= h.empno )
select empno,lvl, ename, mgr,connect_By_is_leaf,
LTRIM(SYS_CONNECT_BY_PATH(empno, ‘-‘), ‘-‘) AS path
from emp_mgr_hierarchy
START WITH mgr IS NULL
CONNECT BY mgr = PRIOR empno;
