Oracle Hierarchical Query

Question

I have table which holds a company hierarchy. It was decided to use this flat table as there is no defined number of levels in the company. The table workes great, and if you were to use cascading lists on a client it's perfect. However, I need to see a "section", and all other "sections" owned by it. Hopefully the information below will gove you an idea of what I need to be able to do.

Table Def

create table SECTION
(
  SECTION_ID       NUMBER(38) not null,
  SECTION_NAME     VARCHAR2(75) not null,
  SECTION_MANAGER  NUMBER(6) not null,
  SECTION_LEVEL    NUMBER(3) not null,
  OWNER_SECTION_ID NUMBER(38)
)

Data

1   IT                    901763    2   0
2   Business Systems             904241 3   1
3   Business Analysis            900813 4   2
4   Development          900976 4   2
5   Testing                   907052    4   2
6   Systems Architecture    908012  4   2
7   Mobilisation             904241 4   2
8   Operations           900885 2   0
9   Area 2                    900456    3   8
0   Executive                          1    0   0

What I need to see

0   Executive                          1    8   Operations
0   Executive                          1    1   IT
0   Executive                          1    0   Executive
0   Executive                          1    2   Business Systems
0   Executive                          1    7   Mobilisation
0   Executive                          1    6   Systems Architecture
0   Executive                          1    4   Development
0   Executive                          1    3   Business Analysis
0   Executive                          1    5   Testing
0   Executive                          1    9    Area 2
1   IT                    901763    2   Business Systems
1   IT                    901763    7   Mobilisation
1   IT                    901763    6   Systems Architecture
1   IT                    901763    4   Development
1   IT                    901763    3   Business Analysis
1   IT                    901763    5   Testing
2   Business Systems             904241 7   Mobilisation
2   Business Systems             904241 6   Systems Architecture
2   Business Systems             904241 4   Development
2   Business Systems             904241 3   Business Analysis
2   Business Systems             904241 5   Testing
8   Operations           900885 9    Area 2
7   Mobilisation             904241     
6   Systems Architecture    908012      
4   Development          900976     
3   Business Analysis            900813     
5   Testing                   907052        
9    Area 2                   900456

I could do this in the C# on the client side, but I would really like to have it as a view on the database.

Can somebody please help me with this. Is it even possible?

If you need anything clarified, please leave a comment and I'll try to provide more information.

Answer 1

Yes, it is possible. You need to use Oracle CONNECT BY syntax. Refer here. Sorry for not sharing the SQL as I can't check it myself.

Answer 2

This solution produces a result which resembles the one in the problem specification.

select
    connect_by_root section_id section_id,
    connect_by_root section_name section_name,
    connect_by_root section_manager section_manager,
    section_id subsection_id,
    section_name subsection_name
from
    section
connect by nocycle
    prior section_id = owner_section_id

The requested solution generates 28 rows when executed against the sample data.

Note that in the sample result, Executive appears as a subsection of itself, while IT, Business Systems, and Operations (which, like Executive, have other subsections too) do not. This solution produces the 3 additional rows.

In addition, note that Executive is its own owner. I am of the belief that cycles should not be permitted in a graph unless the evils they expose us to are the most reasonable way to achieve some required functionality. If there were no such cycle in the graph, the nocycle keyword in the query should be eliminated.