Oracle: How do I query a Hierarchical table?

Question

Background

This is for the construction of some views we'll be using for reporting.

I have an table of locations, the key fields being "location" and "parent".

The structure that these two fields create, level-wise, are along the lines of Company Name --> Campus Name --> Building Name --> Floor Name --> Room Name. Company name remains the same and Campus name remains the same in this case.

The structure of locations generally looks like this:

                                 +-----------+
                                 | Org. Name |
                                 +-----+-----+
                                       |
                                 +-----v-----+
           +--------------------+|Campus Name|+---+--+-------------+
           |                     +--+--------+    |                |
           |                        |             |                |
           |                        |             |                |
        +--+-----+           +------+-+        +--+----+       +---+---+
    +--+| BLDG-01|+--+       | BLDG-02|        |BLDG-03|       |Grounds|
    |   +--------+   |       +--------+        +-------+       +-------+
  +-+------+   +-----+--+
  |Floor-01|   |Basement+-------+
  +-+------+   +--------+       |
    |                           |
    |                           |
    | +----------+      +-------+--+
    +-+Room 1-001|      |Room B-002|
      +----------+      +----------+

Every location links back to its parent location, which is ultimately the organization name. Currently, there is only one organization and one campus.

Goals

• I would like to be able to query all locations beneath any given location at the "Building" level. This is so I can return things like how many workorders have been performed for any location within a given building.
• I would like to be able to determine which sub-location belongs to which building. Essentially the reverse; I would like to go from any level beneath the building level and trace back up to what the building is.
• I would like this to be in a view. That means, I would like to have a table that for every item at the "building" level, lists the building in the left-hand column and all possible locations UNDER that building in the right-hand column. This way I'd have a list that I could query at any time to find which locations are part of which building.

Attempts and Doing it Right

I've attempted to do this through horribly constructed views, UNION queries, etc. -- which all have seemed like a bad idea. I know Oracle possesses a mechanism for this through "CONNECT BY"; I'm just not sure how to make use of it.

Answer 1

FrusteratedWithFormsDesigner has the right direction(+1). Here is what I think you are looking for specifically.

CREATE OR REPLACE VIEW BuildingSubs AS
   SELECT connect_by_root location "Building", location "SubLocation"
   FROM some_table l
   START WITH l.Location IN 
      (
         SELECT location FROM
         (
         SELECT level MyLevel, location FROM some_table 
         START WITH parent IS NULL 
         CONNECT BY PRIOR location=parent
         )
         WHERE MyLevel=3   
      )
   CONNECT BY PRIOR l.location = l.parent;

select * from BuildingSubs; 

Building             SubLocation        
-------------------- --------------------
BLDG-01              BLDG-01              
BLDG-01              Basement             
BLDG-01              Room B-002           
BLDG-01              Floor-01             
BLDG-01              Room 1-001           
BLDG-02              BLDG-02              
BLDG-03              BLDG-03              
Grounds              Grounds              

The view accomplishes all three goals. You can query it for a building to find everything it contains and you can query it for a sub-location to find what building it is in.

drop table some_table;
create table some_table (Location Varchar2(20), Parent Varchar2(20));

insert into some_table values ('Org. Name',NULL);
insert into some_table values ('MAINCAMPUS','Org. Name');
insert into some_table values ('BLDG-01','MAINCAMPUS');
insert into some_table values ('BLDG-02','MAINCAMPUS');
insert into some_table values ('BLDG-03','MAINCAMPUS');
insert into some_table values ('Grounds','MAINCAMPUS');
insert into some_table values ('Floor-01','BLDG-01');
insert into some_table values ('Basement','BLDG-01');
insert into some_table values ('Room B-002','Basement');
insert into some_table values ('Room 1-001','Floor-01');

If you don't want to count the building itself as one of the sub-locations you can wrap the existing query in one the eliminates entries in which the building and sublocation are the same.

Answer 2

CONNECT BY is the correct way to handle data that is naturally recursive.

I don't know what your table looks like but maybe something like:

SELECT *
FROM some_table st
START WITH st.location = 'BLDG-01'
CONNECT BY PRIOR st.location = st.parent;

This should get nodes under "BLDG-01".

The START WITH clause is your base case.

Another explanation (aside from the Oracle one which I assume you've already read and had trouble with, it's probably very terse):

http://www.adp-gmbh.ch/ora/sql/connect_by.html

Also:

http://psoug.org/reference/connectby.html

And:

http://www.oradev.com/connect_by.jsp

Answer 3

I'm not sure I understand your question completely, but maybe something like this:

select location, 
       parent,
       sys_connect_by_path(location, '/') as item_list,
       case level
         when 1 then 'building'
         when 2 then 'floor'
         when 3 then 'room'
       end as item_type
from some_table 
start with parent = 'MAINCAMPUS'
connect by prior location = parent;

This will show you the hierarchy for each location