Effectively Using/Work with CONNECT BY and START WITH Clauses in PL/SQL Asp.Net C#

The start with .. connect by clause can be used to select data that has a hierarchical relationship, after implementing the clause START WITH and CONNECT BY. You can use the reserved word level in the select clause. Witch for those records where we are talking about the root row it will show 1. And for the rest of records it will show 2,3,etc.

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 Worker_id, Supervisor_id, Worker_name
2  FROM Employee
3  START WITH Worker_id = 1
4  CONNECT BY PRIOR Worker_id = Supervisor_id;
Worker_id  Supervisor_id  Worker_name
---------- -------------  ------------
1              2               Petter