2 0
Read Time:1 Minute, 50 Second

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.

Snowflake 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:

Oracle Query

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


CONNECT BY mgr = PRIOR empno;

Snowflake Output

Average Rating

5 Star
4 Star
3 Star
2 Star
1 Star

Leave a Reply

Your email address will not be published. Required fields are marked *