Using Developing where clause with Hierarchical Query in PL/SQL Asp.Net C#

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.

WHERE clause: whether you are querying, modifying, or deleting data, the WHERE clause is the mechanism for identifying what data you want to work with it is used to extract only those records that fulfill a specified criterion. The WHERE clause is an optional part of the SELECT statement. However, if the WHERE clause is absent, the resulting rowset can be extremely large because all rows of the virtual table defined by the FROM clause are returned in the resulting rowset. It is difficult to explain the basic syntax for the WHERE clause, so instead, we'll take a look at some examples.

When the user wants to retrieve tuples based on certain predicates then the user has to use the WHERE clause. Consider the query Find all the loan numbers for the loan made at the SARASWATIH PURAM branch and loan amount > = Rs 5000 .

This query can be written in SQL as

select loan-no from loan
where br_name = SARASWATHI PURAM and amount > 5000

The question raise in you mind that does we use this both statement together at a same time. So, the answer is yes you can use this two statement of PL/SQL at a same time in a single query to fetch out the specific output from the database.

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(' ', 3 * LEVEL - 1) || Worker_name
3  FROM employee
4  WHERE salary <= 6000
5  START WITH Worker_id = 1
6  CONNECT BY PRIOR Worker_id = Supervisor_id;
WHERE salary <= 6000
*
ERROR at line 4:
ORA-00904: "SALARY": invalid identifier

Hope this helps,