Creating Developing Working with Hierarchical Query's in PL/SQL Asp.Net C#

In a hierarchical query you may wish to traverse the tree either upwards or downwards, the hierarchical query clause is used to select the rows in a hierarchical order, If a table contains hierarchical data. Data can easily be made to fit into a relational table by using a self-referential relationship making use of hierarchical queries. With the hierarchical queries support in Enterprise DB complex hierarchical operations can be constructed on tree-structured data.

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.

Oracle evaluates the hierarchical query in the following way:

The result rowset is initialized with the rows determined by the START WITH clauseAll rows for which the CONNECT_BY condition is true are added to the result rowset.Step 2 is repeated until no further rows match.

The following are the Clauses which is used only in hierarchical queries:

CONNECT BYPRIOR START WITHSYS_CONNECT_BY_PATHCONNECT_BY_ROOTCONNECT_BY_ISLEAF

Example of an Hierarchical Query:

SQL> create table Worker
2  ( Workerno            NUMBER(4)    constraint E_PK priCutlar key
3  , Worker_name      VARCHAR2(8)
4  , init                    VARCHAR2(5)
5  , job                    VARCHAR2(8)
6  , mgr                   NUMBER(4)
7  , bdate                DATE
8  , sal                    NUMBER(6,2)
9  , comm                NUMBER(6,2)
10  , deptno              NUMBER(2)    default 10
11  ) ;

Table created.

SQL> insert into Worker values(1,'Joergiya','N',   'TRAINER', 13,date '1965-12-17',  800 , NULL,  20);
1 row created.
SQL> insert into Worker values(2,'Yalric','JAM', 'Tester',6,date '1961-02-20',  1600, 300,   30);
1 row created.
SQL> insert into Worker values(3,'Joy','TF' ,  'Tester',6,date '1962-02-22',  1250, 500,   30);
1 row created.
SQL> insert into Worker values(4,'Thomas','JM',  'Designer', 9,date '1967-04-02',  2975, NULL,  20);
1 row created.
SQL> insert into Worker values(5,'Cutlar','P',  'Tester',6,date '1956-09-28',  1250, 1400,  30);
1 row created.
SQL> insert into Worker values(6,'Nikab','R',   'Designer', 9,date '1963-11-01',  2850, NULL,  30);
1 row created.
SQL> insert into Worker values(7,'Branny','AB',  'Designer', 9,date '1965-06-09',  2450, NULL,  10);
1 row created.
SQL> insert into Worker values(8,'Janny','SCJ', 'TRAINER', 4,date '1959-11-26',  3000, NULL,  20);
1 row created.
SQL> insert into Worker values(9,'Joyson','CC',   'Designer',NULL,date '1952-11-17',  5000, NULL,  10);
1 row created.
SQL> insert into Worker values(10,'Take','JJ', 'Tester',6,date '1968-09-28',  1500, 0,     30);
1 row created.
SQL> insert into Worker values(11,'Aidam','AA',  'TRAINER', 8,date '1966-12-30',  1100, NULL,  20);
1 row created.
SQL> insert into Worker values(12,'Thomas','R',   'MAidamger',   6,date '1969-12-03',  800 , NULL,  30);
1 row created.
SQL> insert into Worker values(13,'Robin','MG',   'TRAINER', 4,date '1959-02-13',  3000, NULL,  20);
1 row created.
SQL> insert into Worker values(14,'Mike','TJA','MAidamger',   7,date '1962-01-23',  1300, NULL,  10);
1 row created.
SQL>
SQL> select  lpad(' ',2*level-1)||Worker_name as Worker_name
2  from    Worker
3  start   with mgr is null
4  connect by nocycle prior Workerno = mgr;

Worker_name
--------------------
Joyson
Thomas
Janny
Aidam
Robin
Joergiya
Nikab
Yalric
Joy
Cutlar
Take
Thomas
Branny
Mike

14 rows selected.

Hope this helps,happy coding.