How to remove a branch with in a hierarchy of data in Oracle?

OracleSoftware & CodingProgramming

Problem Statement: You need to traverse the hierarchy data from top to bottom but doesn’t want a particular branch in the output.

Solution: We will look at couple of examples for this problem statement.

Oracle provides CONNECT BY clause to specify a hierarchical query i.e. how to connect the parent nodes and child nodes and the PRIOR operator to define the join condition/s between the parent nodes, and the LEVEL pseudo-column to indicate how far from the root/parent row the current row is.

Additionally, we can use the START WITH clause to indicate where to start the tree navigation. We must use the PRIOR operator to specify the column/s in the parent row that have the same values as the linking columns in the child row.

In order to loop through the entire hieratchy, remove the START WITH clause. This will fetch all the students with their managers, and their manager’s manager, and so on, until the top manager/director.

In this example, I will be using students data to demonstrate the solution for the problem statement.

Example

SELECT student_id, first_name, last_name FROM students ; 

Output

100 SMITH       JAMES 101 JOHNSON     JOHN 102 WILLIAMS    ROBERT 103 BROWN       MICHAEL 104 JONES       WILLIAM 105 MILLER      DAVID

Assume you does not want to see “BROWN MICHAEL” and his subordinates in this report which means we need to remove/prune an entire branch from the tree. To do this, we must filter out the top of the branch to be removed in the CONNECT BY clause as an additional condition shown below.

SQL:

/*
     Function - Remove the branch - "BROWN MICHAEL" from the output
  Tables Used - students
         Data - Documented below
*/
SELECT student_id,
       level,
       lpad('-',(level-1)*3) || first_name || ' ' || last_name AS full_name
  FROM students
START WITH professor_id IS null
CONNECT BY professor_id = PRIOR student_id
AND NOT (first_name || ' ' || last_name = 'BROWN MICHAEL' )
ORDER SIBLINGS by first_name, last_name;

Output

100 1   SMITH JAMES 101 2     -JOHNSON JOHN 108 3        -RODRIGUEZ JOSEPH 111 4           -ANDERSON DANIEL 110 4           -MARTINEZ CHRISTOPHER 112 4           -TAYLOR PAUL 113 4           -THOMAS MARK 109 4           -WILSON THOMAS 102 2     -WILLIAMS ROBERT

if we want to just ignore the parent but still include the childs i.e. exclude only BROWN MICHAEL" but still include his subordinate students, the filter should be in the WHERE clause instead, as follows:

SQL:

/*
     Function - Remove only the branch head - BROWN MICHAEL from the output
  Tables Used - students
         Data - Documented below
*/
SELECT student_id,
       level,
       lpad('-',(level-1)*3) || first_name || ' ' || last_name AS full_name
  FROM students
WHERE NOT (first_name || ' ' || last_name = 'BROWN MICHAEL' )
START WITH professor_id IS null
CONNECT BY professor_id = PRIOR student_id
ORDER SIBLINGS by first_name, last_name;

Output

100 1   SMITH JAMES 101 2     -JOHNSON JOHN 108 3        -RODRIGUEZ JOSEPH 111 4           -ANDERSON DANIEL 110 4           -MARTINEZ CHRISTOPHER 112 4           -TAYLOR PAUL 113 4           -THOMAS MARK 109 4           -WILSON THOMAS 102 2     -WILLIAMS ROBERT 106 4           -DAVIS RICHARD 107 4           -GARCIA CHARLES 104 4           -JONES WILLIAM

Data Preparation: Data used for the problem is shown below. The data is completely made up for demonstration purpose.

Example

DROP TABLE students;
COMMIT;

CREATE TABLE students
    ( student_id     NUMBER(6)
    , first_name     VARCHAR2(20)
    , last_name      VARCHAR2(25)
    , email          VARCHAR2(40)
    , phone_number   VARCHAR2(20)
    , join_date      DATE
    , class_id       VARCHAR2(20)  
    , fees           NUMBER(8,2)
    , professor_id   NUMBER(6)
    , department_id  NUMBER(4)
    ) ;

Example

CREATE UNIQUE INDEX stu_id_pk ON students (student_id) ;
INSERT INTO students VALUES (100,'SMITH','JAMES','SMITH.JAMES@notreal.com','111.111.1245',TO_DATE('17-06-2003','DD-MM-YYYY'),'INS_CHAIRMAN',24000,NULL,NULL);
INSERT INTO students VALUES (101,'JOHNSON','JOHN','JOHNSON.JOHN@notreal.com','111.111.1246',TO_DATE('21-09-2005','DD-MM-YYYY'),'INS_VP',17000,100,90);
INSERT INTO students VALUES (102,'WILLIAMS','ROBERT','WILLIAMS.ROBERT@notreal.com','111.111.1247',TO_DATE('13-01-2001','DD-MM-YYYY'),'INS_VP',17000,100,90);
INSERT INTO students VALUES (103,'BROWN','MICHAEL','BROWN.MICHAEL@notreal.com','111.111.1248',TO_DATE('03-01-2006','DD-MM-YYYY'),'INS_STAFF',9000,102,60);
INSERT INTO students VALUES (104,'JONES','WILLIAM','JONES.WILLIAM@notreal.com','111.111.1249',TO_DATE('21-05-2007','DD-MM-YYYY'),'INS_STAFF',6000,103,60);
INSERT INTO students VALUES (105,'MILLER','DAVID','MILLER.DAVID@notreal.com','111.111.1250',TO_DATE('25-06-2005','DD-MM-YYYY'),'INS_STAFF',4800,103,60);
INSERT INTO students VALUES (106,'DAVIS','RICHARD','DAVIS.RICHARD@notreal.com','111.111.1251',TO_DATE('05-02-2006','DD-MM-YYYY'),'INS_STAFF',4800,103,60);
INSERT INTO students VALUES (107,'GARCIA','CHARLES','GARCIA.CHARLES@notreal.com','111.111.1252',TO_DATE('07-02-2007','DD-MM-YYYY'),'INS_STAFF',4200,103,60);
INSERT INTO students VALUES (108,'RODRIGUEZ','JOSEPH','RODRIGUEZ.JOSEPH@notreal.com','111.111.1253',TO_DATE('17-08-2002','DD-MM-YYYY'),'CL_PHY',12008,101,100);
INSERT INTO students VALUES (109,'WILSON','THOMAS','WILSON.THOMAS@notreal.com','111.111.1254',TO_DATE('16-08-2002','DD-MM-YYYY'),'CL_MATH',9000,108,100);
INSERT INTO students VALUES (110,'MARTINEZ','CHRISTOPHER','MARTINEZ.CHRISTOPHER@notreal.com','111.111.1255',TO_DATE('28-09-2005','DD-MM-YYYY'),'CL_MATH',8200,108,100);
INSERT INTO students VALUES (111,'ANDERSON','DANIEL','ANDERSON.DANIEL@notreal.com','111.111.1256',TO_DATE('30-09-2005','DD-MM-YYYY'),'CL_MATH',7700,108,100);
INSERT INTO students VALUES (112,'TAYLOR','PAUL','TAYLOR.PAUL@notreal.com','111.111.1257',TO_DATE('07-03-2006','DD-MM-YYYY'),'CL_MATH',7800,108,100);
INSERT INTO students VALUES (113,'THOMAS','MARK','THOMAS.MARK@notreal.com','111.111.1258',TO_DATE('07-12-2007','DD-MM-YYYY'),'CL_MATH',6900,108,100);

COMMIT;
raja
Updated on 04-Dec-2020 10:15:53

Advertisements