Oracle hierarchical query

Question

I have a table that contains a hierarchy of companies. It was decided to use this flat table, as the company did not have a certain number of levels. The table works fine, and if you use cascading lists on the client, it will be fine. However, I need to see the “section” and all the other “sections” that it owns. I hope the information below gives you an idea of ​​what I need to do.

Table defect

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 C # on the client side, but I would really like it to be like a view in a database.

Can someone please help me with this. Is it possible?

If you need to clarify something, leave a comment, and I will try to provide additional information.

Answer 1

This solution gives a result similar to the result in the task 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 lines when executed against sample data.

Note that in the sample result, Executive appears as its unit, and IT , Business Systems and Operations (which, like Executive , also applies to other sections). This solution creates 3 extra lines.

Also note that Executive is its owner. I believe that loops should not be allowed on the chart, unless those flaws to which they are exposed are the most reasonable way to achieve some of the required functionality. If there was no such cycle on the graph, the nocycle keyword in the request should be eliminated.

Answer 2

Yes it is possible. You need to use the Oracle CONNECT BY syntax. See here . Sorry for not using SQL as I cannot check it myself.