select hierarchy question

We have a small SQL-hierargy related problem with our Oracle 7.3 database. We want to have a *single* query returning all the employees working under a certain department taking child-departments into account.

Here the simplified tables:

table DEPARTMENT (dep_id number primary key, dep_name varchar2(60), dep_parent_id number)

column dep_parent_id refences a dep_id in the same table DEPARTMENT, department_id's are random and cannot be changed.

table EMPLOYEE (emp_name varchar2(60) primary key, dep_id number)

The dep_id references the PK of table DEPARTMENT.

The question is: give me all the employees working under department X

so with DEPARTMENTfilled like this:

1, company, null
2, sales, 1
3, marketing, 1
4, region a sales, 2
5, region b sales, 2
6, region a sales big customers, 4
7, region a sales small customers, 4

Give me all the employees working under department "sales" results in all the employes wokng for departments 2,4,5,6 and 7.

• We tried the connect prior-clause but dit not succeed.
• The problem is similar to a the question: what are all the system privileges of "scott"

Anybody having a suggestion?

Thanks,
Peter Grasland Received on Tue May 28 2002 - 15:08:28 CDT