Re: Children with multiple parents
From: ddf <oratune_at_msn.com>
Date: Fri, 2 Jan 2009 13:34:00 -0800 (PST)
Message-ID: <56b9aa63-b40a-4258-bf25-b52c51d33193_at_v42g2000yqj.googlegroups.com>
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...
>
> DMX
> DB1
> APPSERVER1
> APACHE1
> APPSERVER2
> APACHE1
> APACHE2
> DB2
> APPSERVER2
> APPSERVER3
> DB3
> APPSERVER1
> 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
Date: Fri, 2 Jan 2009 13:34:00 -0800 (PST)
Message-ID: <56b9aa63-b40a-4258-bf25-b52c51d33193_at_v42g2000yqj.googlegroups.com>
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...
>
> DMX
> DB1
> APPSERVER1
> APACHE1
> APPSERVER2
> APACHE1
> APACHE2
> DB2
> APPSERVER2
> APPSERVER3
> DB3
> APPSERVER1
> 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 /
ERROR:
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