All about Oracle connect by

oracle-connectby.sql

/*
It builds a hierarchical query. 
There are 2 components to it:
"start with" -- this identifies all LEVEL=1 nodes in the tree
"connect by" -- describes how to walk from the parent nodes above to their children and their childrens children.
*/
select ename, empno, mgr from emp;
select lpad(' ',level*2,' ')||ename ename, 
sys_connect_by_path(business_code, '/') path,
connect_by_isleaf is_leaf,
connect_by_root root_path,
LEVEL AS level_id,
empno, mgr
from emp
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR

SELECT a.business_code, b.business_desc, a.parent_business_code, b.tier,  b.ge_gecs_ind FROM final_parent_child;

SELECT c.*,
  sys_connect_by_path(business_code, '/') path,
  connect_by_root business_code root_path,
  connect_by_isleaf is_leaf,
  LEVEL AS level_id
FROM final_parent_child c
  START WITH parent_business_code IS NULL
  CONNECT BY PRIOR business_code = parent_business_code

/*
--CONNECT_BY_ROOT —returns the root of the hierarchy for the current row; this greatly simplifies our query. (See below for an example).
--CONNECT_BY_ISLEAF —is a flag to tell you if the current row has child rows.
--CONNECT_BY_ISCYCLE —is a flag to tell you if the current row is the beginning of an infinite loop in your hierarchy. For example, if A is the parent of B, B is the parent of C, and C is the parent of A, you would have an infinite loop. You can use this flag to see which row or rows are the beginning of an infinite loop in your data.
--NOCYCLE —lets the CONNECT BY query recognize that an infinite loop is occurring and stop without error (instead of returning a CONNECT BY loop error).
*/