I have to get a list of limits for a the sub-parts of a level 1part. Limits for some parts are null, and for those null parts, I have to query the level 2 parts that are on that part and give the MIN of the limits of those level 2 parts. For some of the level 2 parts, I have to get the MIN of their sub-parts (the level 3 parts on that level 2 part) and roll that up to the level 2 aggregate.
What would be your approach?
In writing this, I realized that I could filter the nulled level 1 parts out and UNION it with a query that does the aggregation for nulled level 1 parts, but I want to make sure there's not a better solution. I think I felt a little pigeon holed by the query that I was given originally, which gave the entire tree (lower level parts parts were joined to higher level parts using START WITH and CONNECT BY), and I was trying to compress it via clever SQL, but I could not. Also, I'd like the query to work with n levels of aggregation, not a finite number based on how many times I want to copy-paste my UNIONs and subqueries.
This has to be done in SQL. I can't use PL/SQL.
For simplicity's sake, think of the schema like this:
Assembly table
level1_serial | level2_serial
-----------------------------
1234 | null
123 | 1
123 | 2
123 | 3
Limit table
part_serial | limit
-------------------
1234 | 1000
123 | null
1 | 500
2 | 400
3 | 600
In this case, I would need to return two records as follows
part_serial | limit
-------------------
1234 | 1000
123 | 400