Mega Code Archive
Including Other Conditions in a Hierarchical Query
<![CDATA[
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> -- Including Other Conditions in a Hierarchical Query
SQL>
SQL> SELECT LEVEL,
2 LPAD(' ', 2 * LEVEL - 1) || first_name || ' ' ||
3 last_name AS employee, salary
4 FROM employee
5 WHERE salary <= 50000
6 START WITH employee_id = 1
7 CONNECT BY PRIOR employee_id = manager_id;
LEVEL EMPLOYEE SALARY
---------- ------------------------- ----------
3 Rob Green 40000
3 Jane Brown 45000
4 Henry Heyson 30000
3 John Grey 30000
4 Jean Blue 29000
3 Keith Long 50000
3 Frank Howard 45000
3 Doreen Penn 47000
8 rows selected.
SQL>
SQL>
SQL>
SQL>
SQL> drop table employee;
Table dropped.
SQL>
SQL>
]]>