Re: Children with multiple parents

From: ddf <>
Date: Fri, 2 Jan 2009 13:34:00 -0800 (PST)
Message-ID: <>

On Jan 2, 3:01 pm, Steve Howard <> wrote:
> Hi All,
> 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...
>   DB1
>       APACHE1
>       APACHE1
>       APACHE2
>   DB2
>   DB3
>     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?
> Thanks,
> Steve

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 ORA-01436: SQL> select ename, empno, mgr
  2 from emp
  3 connect by mgr = prior empno
  4 start with empno = 8813
  5 /
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

Original text of this message