Oracle PL/SQL/Hierarchical Query/Nodes Branches
Eliminate an entire branch of nodes from the results of a query, you add an AND clause to your CONNECT BY PRIOR clause
SQL>
SQL> CREATE TABLE employee (
2 employee_id INTEGER,
3 manager_id INTEGER,
4 first_name VARCHAR2(10) NOT NULL,
5 last_name VARCHAR2(10) NOT NULL,
6 title VARCHAR2(20),
7 salary NUMBER(6, 0)
8 );
Table created.
SQL>
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 1 ,0 , "James" ,"Smith" ,"CEO",800000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 2 , 1 ,"Ron" ,"Johnson","Sales Manager",600000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 3 , 2 ,"Fred" ,"Hobbs" ,"Sales Person",200000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 4 , 1 ,"Susan" ,"Jones" ,"Support Manager",500000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 5 , 2 ,"Rob" ,"Green" ,"Sales Person", 40000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 6 , 4 ,"Jane" ,"Brown" ,"Support Person",45000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 7 , 4 ,"John" ,"Grey" ,"Support Manager",30000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 8 , 7 ,"Jean" ,"Blue" ,"Support Person",29000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 9 , 6 ,"Henry" ,"Heyson" ,"Support Person",30000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 10 , 1 ,"Kevin" ,"Black" ,"Ops Manager",100000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 11 , 10 ,"Keith" ,"Long" ,"Ops Person",50000);
1 row created.
SQL>
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 12 , 10 ,"Frank" ,"Howard" ,"Ops Person",45000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 13 , 10 ,"Doreen" ,"Penn" ,"Ops Person",47000);
1 row created.
SQL>
SQL>
SQL>
SQL>
SQL> select * from employee;
EMPLOYEE_ID MANAGER_ID FIRST_NAME LAST_NAME TITLE SALARY
----------- ---------- ---------- ---------- -------------------- ----------
1 0 James Smith CEO 800000
2 1 Ron Johnson Sales Manager 600000
3 2 Fred Hobbs Sales Person 200000
4 1 Susan Jones Support Manager 500000
5 2 Rob Green Sales Person 40000
6 4 Jane Brown Support Person 45000
7 4 John Grey Support Manager 30000
8 7 Jean Blue Support Person 29000
9 6 Henry Heyson Support Person 30000
10 1 Kevin Black Ops Manager 100000
11 10 Keith Long Ops Person 50000
12 10 Frank Howard Ops Person 45000
13 10 Doreen Penn Ops Person 47000
13 rows selected.
SQL>
SQL>
SQL> --Eliminate an entire branch of nodes from the results of a query, you add an AND
clause to your CONNECT BY PRIOR clause
SQL>
SQL> SELECT LEVEL,
2 LPAD(" ", 2 * LEVEL - 1) || first_name || " " ||
3 last_name AS employee
4 FROM employee
5 START WITH employee_id = 1
6 CONNECT BY PRIOR employee_id = manager_id
7 AND last_name != "Johnson";
LEVEL EMPLOYEE
---------- -------------------------
1 James Smith
2 Susan Jones
3 Jane Brown
4 Henry Heyson
3 John Grey
4 Jean Blue
2 Kevin Black
3 Keith Long
3 Frank Howard
3 Doreen Penn
10 rows selected.
SQL>
SQL>
SQL>
SQL> drop table employee;
Table dropped.
SQL>
SQL>
Eliminating Nodes and Branches from a Hierarchical Query
SQL> CREATE TABLE employee (
2 employee_id INTEGER,
3 manager_id INTEGER,
4 first_name VARCHAR2(10) NOT NULL,
5 last_name VARCHAR2(10) NOT NULL,
6 title VARCHAR2(20),
7 salary NUMBER(6, 0)
8 );
Table created.
SQL>
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 1 ,0 , "James" ,"Smith" ,"CEO",800000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 2 , 1 ,"Ron" ,"Johnson","Sales Manager",600000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 3 , 2 ,"Fred" ,"Hobbs" ,"Sales Person",200000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 4 , 1 ,"Susan" ,"Jones" ,"Support Manager",500000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 5 , 2 ,"Rob" ,"Green" ,"Sales Person", 40000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 6 , 4 ,"Jane" ,"Brown" ,"Support Person",45000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 7 , 4 ,"John" ,"Grey" ,"Support Manager",30000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 8 , 7 ,"Jean" ,"Blue" ,"Support Person",29000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 9 , 6 ,"Henry" ,"Heyson" ,"Support Person",30000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 10 , 1 ,"Kevin" ,"Black" ,"Ops Manager",100000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 11 , 10 ,"Keith" ,"Long" ,"Ops Person",50000);
1 row created.
SQL>
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 12 , 10 ,"Frank" ,"Howard" ,"Ops Person",45000);
1 row created.
SQL>
SQL> insert into employee (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY)
2 values( 13 , 10 ,"Doreen" ,"Penn" ,"Ops Person",47000);
1 row created.
SQL>
SQL>
SQL>
SQL>
SQL> select * from employee;
EMPLOYEE_ID MANAGER_ID FIRST_NAME LAST_NAME TITLE SALARY
----------- ---------- ---------- ---------- -------------------- ----------
1 0 James Smith CEO 800000
2 1 Ron Johnson Sales Manager 600000
3 2 Fred Hobbs Sales Person 200000
4 1 Susan Jones Support Manager 500000
5 2 Rob Green Sales Person 40000
6 4 Jane Brown Support Person 45000
7 4 John Grey Support Manager 30000
8 7 Jean Blue Support Person 29000
9 6 Henry Heyson Support Person 30000
10 1 Kevin Black Ops Manager 100000
11 10 Keith Long Ops Person 50000
12 10 Frank Howard Ops Person 45000
13 10 Doreen Penn Ops Person 47000
13 rows selected.
SQL>
SQL> -- Eliminating Nodes and Branches from a Hierarchical Query
SQL>
SQL> SELECT LEVEL,
2 LPAD(" ", 2 * LEVEL - 1) || first_name || " " ||
3 last_name AS employee
4 FROM employee
5 WHERE last_name != "Johnson"
6 START WITH employee_id = 1
7 CONNECT BY PRIOR employee_id = manager_id;
LEVEL EMPLOYEE
---------- -------------------------
1 James Smith
3 Fred Hobbs
3 Rob Green
2 Susan Jones
3 Jane Brown
4 Henry Heyson
3 John Grey
4 Jean Blue
2 Kevin Black
3 Keith Long
3 Frank Howard
3 Doreen Penn
12 rows selected.
SQL>
SQL>
SQL>
SQL> drop table employee;
Table dropped.
SQL>
SQL>