Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
+1 for ASCII art –  Vincent Malgrat Jun 29 '12 at 10:12
    
How are "root" nodes identified? Is parent NULL for them? How do you identify a "building level"? –  a_horse_with_no_name Jul 3 '12 at 13:09
    
@a_horse_with_no_name logically, I suppose the "building" level would be anything with a parent that is the campus name, i.e. anything with a parent of "MAINCAMPUS". The root of all the nodes is "COMPANYNAME", which is the parent of "MAINCAMPUS", and all buildings (plus "grounds") have MAINCAMPUS as a parent. –  SeanKilleen Jul 3 '12 at 15:57
    
wow! how did you create that!! Google for "Adjacency Model in SQL" you will be all set –  srini.venigalla Jul 3 '12 at 19:58
add comment

3 Answers 3

up vote 3 down vote accepted

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.

share|improve this answer
    
Leigh, this was exactly it. Thank you for the help! –  SeanKilleen Jul 3 '12 at 19:39
add comment

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

share|improve this answer
    
Thanks for the response! I'm getting it enough to realize that I don't think I phrased my question well. My table structure has two columns -- "location" and "parent". The hierarchy these create is defined by my ascii chart. I would like to construct a view that shows, for each location at the "building" level, all locations underneath its branch. My goal is to be able to query a building and get all its sub-locations, or query a sublocation and see which building it belongs to, through a view (so no hard-defined "building-x" in the query). Any help would be greatly appreciated! –  SeanKilleen Jul 3 '12 at 12:43
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.