connect_by_isleaf

Oracle10g connect_by_isleaf

The pseudo-column, connect_by_isleaf, returns 1 if the current row is a leaf (child and it is not father) in the tree defined by the CONNECT BY condition. If the current row is not a leaf in the current tree, it returns 0. This information indicates whether a given row can be further expanded to show more of the hierarchy.

connect_by_isleaf Example
The following example shows the first three levels of the scott.emp table, indicating for each row whether it is a leaf row ( which is indicated by 1 in the Leaf column) or whether it has child rows (which is indicated by 0 in the Leaf column):

SELECT ename "Employee", CONNECT_BY_ISLEAF "Leaf", LEVEL,
SYS_CONNECT_BY_PATH(ename,'/') "Path" FROM scott.emp
WHERE level <= 3 AND deptno = 10 START WITH ename ='KING'
CONNECT BY nocycle PRIOR empno = mgr AND LEVEL <= 4
;