Re: Children with multiple parents
From: Steve Howard <stevedhoward_at_gmail.com>
Date: Mon, 5 Jan 2009 10:49:01 -0800 (PST)
Message-ID: <b2575eda-9073-48d2-ba13-db8a9c98ce54_at_f40g2000pri.googlegroups.com>
On Jan 2, 4:34 pm, ddf <orat..._at_msn.com> wrote:
> 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
Date: Mon, 5 Jan 2009 10:49:01 -0800 (PST)
Message-ID: <b2575eda-9073-48d2-ba13-db8a9c98ce54_at_f40g2000pri.googlegroups.com>
On Jan 2, 4:34 pm, ddf <orat..._at_msn.com> wrote:
> 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
Thanks, David. I will keep digging and let you know what I find. Received on Mon Jan 05 2009 - 12:49:01 CST