Deleting a Node from a Hierarchical Query in PL/SQL Asp.Net C#

"Working with Hierarchical Query's in PL/SQL".The following list defines terms that we'll use often when working with hierarchical data: Node: A row in a table that represents a specific entry in a hierarchical tree structure.Parent: A node that is one level up in a tree.Child: A node that is one level down in a tree.Root: The uppermost node in a hierarchical structure.Leaf: A node with no children, and sometimes called a leaf node.Level: A layer of nodes.

Now let suppose you want to delete a node from the middle, how..? Not to worry this article gives you the solution here I am taking a example of Employee table in which you see how to Eliminate Nodes from a Hierarchical Query.

Example:

SQL> -- create demo table
SQL> create table        Employee(
2    Worker_id            NUMBER(3),
3    Worker_name       VARCHAR2(15 BYTE),
4    Sal_pack             NUMBER(6),
5    REGION               VARCHAR2(1 BYTE),
6    Supervisor_id       NUMBER(3)
7  )
8  /
Table created.
SQL>
SQL> create table Profile (
2    Worker_id         NUMBER(3),
3    Profiletitle         VARCHAR2(20 BYTE)
4  )
5  /
Table created.
SQL>
SQL> insert into Profile (Worker_id, Profiletitle) values (1,'Painter');
1 row created.
SQL> insert into Profile (Worker_id, Profiletitle) values (2,'Cashier');
1 row created.
SQL> insert into Profile (Worker_id, Profiletitle) values (3,'Counsler');
1 row created.
SQL> insert into Profile (Worker_id, Profiletitle) values (4,'Co-ordinator');
1 row created.
SQL> insert into Profile (Worker_id, Profiletitle) values (5,'Human Resorce');
1 row created.
SQL> insert into Profile (Worker_id, Profiletitle) values (6,'Advisor');

1 row created.
SQL>
SQL>
SQL> -- prepare data
SQL> insert into Employee(Worker_id,  Worker_name,      Sal_pack,  REGION, Supervisor_id)
2               values (1,      'Petter',     8000,         'E',    2)
3  /
1 row created.
SQL> insert into Employee(Worker_id,  Worker_name,      Sal_pack,  REGION, Supervisor_id)
2               values (2,      'Fara',      3350,         'W',    3)
3  /
1 row created.
SQL> insert into Employee(Worker_id,  Worker_name,      Sal_pack,  REGION, Supervisor_id)
2               values (3,      'Lui',       5000,         'E',    3)
3  /
1 row created.
SQL> insert into Employee(Worker_id,  Worker_name,      Sal_pack,  REGION, Supervisor_id)
2               values (4,      'Dik',       6000,         'W',    4)
3  /
1 row created.
SQL> insert into Employee(Worker_id,  Worker_name,      Sal_pack,  REGION, Supervisor_id)
2               values (5,      'Jame',      4000,         'E',    4)
3  /
1 row created.
SQL> insert into Employee(Worker_id,  Worker_name,      Sal_pack,  REGION, Supervisor_id)
2               values (6,      'Kalis',     7000,         'W',    5)
3  /  SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2  /
Worker_id   Worker_name     Sal_pack   R Supervisor_id
---------- ---------------  ----------- - -------------
1             Petter              8000      E        2
2             Fara                3350      W       3
3             Lui                  5000      E        3
4             Dik                  6000      W       4
5             Jame               4000      E        4
6             Kalis                7000      W       5 
6 rows selected.
SQL> select * from Profile
2  /
Worker_id  ProfileTITLE
---------- ---------------
1        Painter
2        Cashier
3        Counsler
4        Co-ordinator
5        Human Resorce
6        Advisor 
6 rows selected.
SQL>
SQL> SELECT LEVEL,
2   LPAD(' ', 2 * LEVEL - 1) || ename
3  FROM Employee
4  WHERE ename != 'Kalis'
5  START WITH Worker_id = 1
6  CONNECT BY PRIOR Worker_id = Supervisor_id;
LEVEL          LPAD('',2*LEVEL-1)||Worker_name
1          Petter

Hope this helps,