Oracle - connect_by_prior get root node name of leaf row
- by Tom Beyer
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 :-(