Re: Children with multiple parents
Date: Fri, 2 Jan 2009 13:34:00 -0800 (PST)
On Jan 2, 3:01 pm, Steve Howard <stevedhow..._at_gmail.com> wrote:
> Hi All,
> 10.2.0.4 64 bit Enterprise on SuSE 2.6.16
> I have been working on a small application to allow us to print a
> hierarchical list of affected systems when a change is made to another
> system. For example, when a change is made to our storage system, we
> want a report that may look like the following...
> DB2 (database link)
> I have been struggling to get this to work with a connect by prior
> structure. I ended up writing a pipelined table function that
> provides what we need, but I am fairly sure it is not the best way to
> do this.
> Before I spend my weekend spinning my wheels, can anyone comment as to
> whether model where multiple children can be related to multiple
> parents (and vice versa) can be handled by connect by prior? If not,
> is there a better way that jumps out at you?
As far as I know the only limitation to connect by prior is if you
have records which point to each other as a parent, which throws an
SQL> select ename, empno, mgr
2 from emp
3 connect by mgr = prior empno
4 start with empno = 8813
ORA-01436: CONNECT BY loop in user data
no rows selected
SQL> I've seen and worked with manufacturing bill-of-materials data where a number of finished assemblies all contained one or two common parts (the child of multiple parents configuration you asked about, I believe) and connect by had no problems displaying the results.
David Fitzjarrell Received on Fri Jan 02 2009 - 15:34:00 CST