Oracle - connect_by_prior get root node name of leaf row

If you are using hierarchical queries with Oracle, it’s not so easy to retrieve the root node name of leaf rows. I already needed this multiple times, so I thought about sharing with you how it works.

First of all we create or example schema:

create table EMPLOYEE
(
  id      number(12),
  name    varchar2(80),
  boss_id number(12)
);

alter table EMPLOYEE
  add constraint EMPLOYEE_PK primary key (ID);

alter table EMPLOYEE
  add constraint EMPLOYEE_BOSS_ID_FK foreign key (BOSS_ID)
  references employee (ID);

This is how it looks like:

rest of the article is coming soon. got lost during software migration :-(

Comments