0

I am making a database to sum up a system of electrical loads, connected via various buses. The idea is to be able to check no bus has its load rating exceeded. I am using APEX and an Oracle database.

I have two tables, LOAD and BUS.

LOAD has the columns IDENTIFIER, PARENT_BUS, POWER

BUS has the colums IDENTIFIER, PARENT_BUS

(This is just a simplification of the tables, but the other data is not relevant to this problem I don't think)

I have created a table structure that looks a little bit like this:

BUS 0 (root bus)
  BUS 1
   -LOAD 1000
   -LOAD 1009
   BUS 101
     -LOAD 1001
     -LOAD 1002
     -LOAD 1007
   BUS 102
     -LOAD 1003
  BUS 2
   BUS 201
     -LOAD 1004
     -LOAD 1008
   BUS 202
     -LOAD 1005
     -LOAD 1006

There are a set of buses that link to another bus in the bus table using a foreign key from BUS.PARENT_BUS to BUS.IDENTIFIER. The loads in the load table also link via a foreign key to a bus: i.e. LOAD.PARENT_BUS to BUS.IDENTIFIER. There would typically be hundreds or even thousands of loads, connected to several dozen buses.

Note that it is possible for a load to attach anywhere in the bus structure, and there can be any level of buses (although I only show two levels in the example - three or more levels would be uncommon but not impossible)

I need to sum all of the loads at each bus level, and have this feed up to the next-higher bus. i.e. for BUS 101 I need the sum of LOAD 1001 + LOAD 1002 + LOAD 1007, then for BUS 1 I need the sum of BUS 101 + BUS 102 + LOAD 1000 + LOAD 1009.

I have tried doing this:

SELECT bus.IDENTIFIER AS "PARENT", bus.PARENT_BUS AS "GRANDPARENT", SUM(load.POWER) AS "POWER", level
FROM BUS_DATA bus
LEFT JOIN LOAD_DATA load
ON load.PARENT_BUS = bus.IDENTIFIER
START WITH bus.PARENT_BUS = '0' -- This is the root that all buses eventually link to
CONNECT BY PRIOR bus.IDENTIFIER = bus.PARENT_BUS
GROUP BY bus.IDENTIFIER, bus.PARENT_BUS, level;

And this gives me

PARENT, GRANDPARENT, POWER, LEVEL
201     2            307.6  2
202     2            189.4  2

Which is correct, because with the data I used to generate this there are no loads attached to BUS 2. I also need to see a row:

PARENT, GRANDPARENT, POWER, LEVEL
2       0            497    1

i.e. the sum of 201 and 202, plus any loads that might be attached directly to BUS 2.

What can I do to have the summed power data feed up through the bus structure to the parent bus?

seanlano
  • 141
  • 5
  • can you post sample create table and inserts along with the exact result you need to see? Also what Oracle version you are using? – Srini V Feb 20 '14 at 04:57

1 Answers1

0

Part of your problem is that you have implemented the hierarchical structure of the buses as an adjacency list which given what you are trying to do and what I assume is the nature of the data (buses are relatively static, loads are relatively dynamic) is not the easiest or most efficient method.

Have a look at this question for the various methods of representing your data. Have a look at the second link in the question to get you started.

Off the cuff, I would suggest you implement a path enumeration storage method since it seems almost inherent in the IDs of the buses.

Community
  • 1
  • 1
Dale M
  • 2,453
  • 1
  • 13
  • 21
  • Hmm, perhaps where I said that the other columns weren't relevant is not quite correct. Both the BUS and LOAD tables have a column for a Circuit Breaker, which is a foreign key to a BREAKER table which then contains information about the breakers. I couldn't get rid of the BUS table, which would have definitely made things easier. Using a Path Enumeration method would be more efficient for finding parents and children but I still need tables for LOAD and for BUS, and I still need a way to recursively sum the loads attached to each bus. I don't see how a Path Enumeration format helps with this. – seanlano Feb 20 '14 at 02:31
  • Sure it does, if the primary key of your buses is something like 1, 1/101, 1/101/1001, 1/101/1002 then a query like SELECT SUM(Load.Power) FROM BUS INNER JOIN LOAD ON BUS.ID=LOAD.BUS WHERE BUS.ID LIKE '1*' will work - You would not actually do it this way, SQL SERVER has native datatype support for this type of HeirachicalID (called HeirachicalID), I am sure Oracle does too. – Dale M Feb 21 '14 at 04:31
  • OK, thanks. That does make a little sense. :P I'll have a look and see if Oracle has this hierarchical type. I was worried I would have to do a lot of string manipulation to get it to work, but maybe not. – seanlano Feb 22 '14 at 05:25
  • Oracle apparently does not have this HeirachicalID. It seems to be unique to SQL Server. So doing this kind of storage in Oracle would require a lot of string manipulation. – seanlano Feb 24 '14 at 03:51
  • You can do it using integers rather than strings; your lowest level busses are the units & 10s, the next level are the 100s & 1000s and so on - each bus therefore carries its entire path in its id. – Dale M Feb 24 '14 at 05:43