Hierarchical data, how to aggregate over levels in hierarchical query?
z37Jun 6 2013 — edited Jun 7 2013Hello,
hopefully someone can help me.
I have data organized in a table("which part was built into what other part from when to when?")
id parent_id build_in build_out
1 NULL NULL NULL
2 1 2010 2012
3 2 2011 2013
4 2 2013 NULL
what the parts are is stored in a separate table.
Now I want to know from when to when what part was build into the top level, in the example i want to know that
1 was trivially a part of 1
2 was a part of 1 from 2010 to 2012
3 was a part of 1 from 2011 to 2012
4 was never a part of 1
I've tried several approaches - If there is a fixed number of levels between the types I'm interested in I can do it using joins and using greatest/least (along with some nvl). Unfortunately that's not always the case (some parts appear on several levels).
If i'm only interested in the parts that were never removed i can get them using a connect by-style query and get the current configuration. But I can't seem to figure out the relevant times connecting a part to the top level in such a query or even filtering out nonsensical combinations (like "4 in 1" in the example above). I could process the retrieved data outside of the database, but i'd prefer not to.
Is there a way to get hierarchical data along with an aggregate(min, max) over all higher levels?