ORACLE Model clause

Played around with the model clause to normalize the EMP hiarchy:
Normal connect by prior:

SELECT 'org_name' AS organisation,
LEVEL AS org_level,
ename FROM emp
CONNECT BY PRIOR empno = mgr
START WITH ename = 'KING'

ORGANISA ORG_LEVEL ENAME
-------- ---------- ----------
org_name 1 KING
org_name 2 JONES
org_name 3 SCOTT
org_name 4 ADAMS
org_name 3 FORD
org_name 4 SMITH
org_name 2 BLAKE
org_name 3 ALLEN
org_name 3 WARD
org_name 3 MARTIN
org_name 3 TURNER
org_name 3 JAMES
org_name 2 CLARK
org_name 3 MILLER

with model clause:

select organisation,
level1, level2, level3, level4 from
( SELECT 'org_name' AS organisation, LEVEL AS org_level, ename
FROM emp CONNECT BY PRIOR empno = mgr
START WITH ename = 'KING')
model
return updated rows
partition by
(organisation)
dimension by (rownum rn)
measures (lpad(' ',10) level1,
lpad(' ',10) level2, lpad(' ',10) level3,
lpad(' ',10) level4, org_level, ename)
rules update
( level1[ANY] = case when org_level[CV()] = 1 then ename [cv()] end,
level2[any] = case when org_level[CV()] = 2 then ename [cv()] end,
level3[any] = case when org_level[cv()] = 3 then ename [cv()] end,
level4[any] = case when org_level[cv()] = 4 then ename [cv()] end )

ORGANISA LEVEL1 LEVEL2 LEVEL3 LEVEL4
-------- ---------- ---------- ---------- ----------
org_name KING
org_name JONES
org_name SCOTT
org_name ADAMS
org_name FORD
org_name SMITH
org_name BLAKE
org_name ALLEN
org_name WARD
org_name MARTIN
org_name TURNER
org_name JAMES
org_name CLARK
org_name MILLER

Let's fill the blanks:

select organisation,
level1, nvl(level2,level1) as level2, nvl(level3,nvl(level2,level1)) as level3,
nvl(level4,nvl(level3,nvl(level2,level1))) as level4 from (
select organisation, last_value(level1 ignore nulls) over (order by rownum) as
level1 , last_value(level2 ignore nulls) over (order by rownum) as level2 ,
decode(level4,null,level3,last_value(level3 ignore nulls) over (order by
rownum)) as level3, level4 from ( select organisation, level1 ,level2,level3,level4 from ( SELECT 'org_name' AS organisation, LEVEL AS
org_level, ename FROM emp CONNECT BY PRIOR empno = mgr START WITH ename = 'KING')
model
return updated rows
partition by (organisation)
dimension by (rownum rn)
measures (lpad(' ',10) level1, lpad(' ',10)
level2, lpad(' ',10) level3,lpad(' ',10) level4, org_level, ename)
rules
update
( level1[ANY] = case when org_level[CV()] = 1 then ename [cv()] end,
level2[any] = case when org_level[CV()] = 2 then ename [cv()] end,
level3[any] = case when org_level[cv()] = 3 then ename [cv()] end,
level4[any] = case when org_level[cv()] = 4 then ename [cv()] end
)))
;

ORGANISA LEVEL1 LEVEL2 LEVEL3 LEVEL4
-------- ---------- ---------- ---------- ----------
org_name KING KING KING KING
org_name KING JONES JONES JONES
org_name KING JONES SCOTT SCOTT
org_name KING JONES SCOTT ADAMS
org_name KING JONES FORD FORD
org_name KING JONES FORD SMITH
org_name KING BLAKE BLAKE BLAKE
org_name KING BLAKE ALLEN ALLEN
org_name KING BLAKE WARD WARD
org_name KING BLAKE MARTIN MARTIN
org_name KING BLAKE TURNER TURNER
org_name KING BLAKE JAMES JAMES
org_name KING CLARK CLARK CLARK
org_name KING CLARK MILLER MILLER

Till Next Time