Re: Connect By hierarchy problem

"Mike C" <michaeljc70_at_hotmail.com> a écrit dans le message de news: 1161267324.784629.195520_at_k70g2000cwa.googlegroups.com...
|
| Charles Hooper wrote:
| > Mike C wrote:
| > > Charles Hooper wrote:
| > > > Mike C wrote:
| > > > > On Oct 17, 12:14 pm, "Charles Hooper" <hooperc2..._at_yahoo.com> wrote:
| > > > > > Mike C wrote:
| > > > > > > On Oct 17, 10:05 am, "Mike C" <michaelj..._at_hotmail.com> wrote:
| > > > > > > > I have a simple table with a hierarchical structure:
| > > > > >
| > > > > > > > CREATE TABLE relation (parent_id number, child_id number);
| > > > > >
| > > > > > > > INSERT INTO relation values (10,3);
| > > > > > > > INSERT INTO relation values (3,2);
| > > > > > > > INSERT INTO relation values (2,1);
| > > > > > > > INSERT INTO relation values (10,5);
| > > > > > > > INSERT INTO relation values (14,11);
| > > > > > > > INSERT INTO relation values (11,17);
| > > > > >
| > > > > > > > I need to a query to return a results that lists every child and it's
| > > > > > > > direct and not direct parent (it's parents parent and so on), Having
| > > > > > > > the level would be nice too For example, I want this back:
| > > > > >
| > > > > > > > Parent_id Child_id
| > > > > >
| > > > > > > > 10 3
| > > > > > > > 3 2
| > > > > > > > 10 5
| > > > > > > > 14 11
| > > > > > > > 11 17
| > > > > > > > 10 2
| > > > > > > > 14 17
| > > > > > > > 2 1
| > > > > > > > 3 1
| > > > > > > > 10 1
| > > > > >
| > > > > > > > I can't seem to get CONNECT BY to do this.
| > > > > >
| > > > > > > What I have so far is this:
| > > > > >
| > > > > > > SELECT * FROM (
| > > > > > > select child_id , (select parent_id from relation r2
| > > > > > > where level=2
| > > > > > > start with r1.child_id= r2.child_id
| > > > > > > connect by prior parent_id=child_id) parent_id, 2 lvl
| > > > > > > FROM relation r1
| > > > > > > UNION
| > > > > > > select child_id , (select parent_id from relation r2
| > > > > > > where level=1
| > > > > > > start with r1.child_id= r2.child_id
| > > > > > > connect by prior parent_id=child_id) parent_id, 1 lvl
| > > > > > > FROM relation r1
| > > > > > > ) where parent_id is not null
| > > > > >
| > > > > > > The problem is that it only handles 2 levels and there really could be
| > > > > > > an inifinte number of levels.How close does this come?
| > > > > > SELECT
| > > > > > PARENT_ID,
| > > > > > CHILD_ID,
| > > > > > LEVEL
| > > > > > FROM
| > > > > > RELATION
| > > > > > CONNECT BY PRIOR
| > > > > > PARENT_ID=CHILD_ID;
| > > > > >
| > > > > > PARENT_ID CHILD_ID LEVEL
| > > > > > ========== ========== ==========
| > > > > > 2 1 1
| > > > > > 3 2 2
| > > > > > 10 3 3
| > > > > > 3 2 1
| > > > > > 10 3 2
| > > > > > 10 3 1
| > > > > > 10 5 1
| > > > > > 14 11 1
| > > > > > 11 17 1
| > > > > > 14 11 2
| > > > > >
| > > > > > SELECT
| > > > > > PARENT_ID,
| > > > > > CHILD_ID,
| > > > > > LEVEL
| > > > > > FROM
| > > > > > RELATION
| > > > > > CONNECT BY PRIOR
| > > > > > CHILD_ID=PARENT_ID;
| > > > > >
| > > > > > PARENT_ID CHILD_ID LEVEL
| > > > > > ========== ========== ==========
| > > > > > 2 1 1
| > > > > > 3 2 1
| > > > > > 2 1 2
| > > > > > 10 3 1
| > > > > > 3 2 2
| > > > > > 2 1 3
| > > > > > 10 5 1
| > > > > > 11 17 1
| > > > > > 14 11 1
| > > > > > 11 17 2
| > > > > >
| > > > > > Charles Hooper
| > > > > > PC Support Specialist
| > > > > > K&M Machine-Fabricating, Inc.
| > > > >
| > > > > That is close, but if you look it lists child_id=2, parent_id=3 2X with
| > > > > a different level.
| > > >
| > > > The reason that you are seeing the repeated values at a different level
| > > > is because Oracle was not told what value to use as starting point of
| > > > the hierarcy - it runs through all of the possibilities. In the first
| > > > SQL statement, the first child_id=2, parent_id=3 was found by first
| > > > running through the child_id=1, parent_id=2 match, and then it found
| > > > that it could connect to child_id=2, parent_id=3 from there. The
| > > > second time this match appeared, it immediately found the child_id=2,
| > > > parent_id=3 match. What this requires is the program which reads the
| > > > output to generate new output that looks for the level to be the same
| > > > or less than the previous level to decide that a new child record has
| > > > been found. For example, using the output of the first SQL statement:
| > > > Read in P=2, C=1, L=1 - P=2, C=1
| > > > Read in P=3, C=2, L=2 - this is still part of the same connect by
| > > > chain, P=3, C=1 holds
| > > > Read in P=10, C=3, L=3 - this is still part of the same connect by
| > > > chain, P=10, C=1 holds
| > > > Read in P=3, C=2, L=1 - this is the start of a new chain, P=3, C=2
| > > > Read in P=10, C=3, L=3 - this is still part of the same connect by
| > > > chain, P=10, C=2 holds
| > > > ...
| > > >
| > > > And then you need to obtain the distinct list of parent, child matches.
| > > > The above can be done using PL/SQL.
| > > >
| > > > Let's look at this using another method, using a likely less efficient
| > > > method using a self join:
| > > > SELECT
| > > > R1.CHILD_ID,
| > > > R1.PARENT_ID P1,
| > > > R2.PARENT_ID P2,
| > > > R3.PARENT_ID P3,
| > > > R4.PARENT_ID P4,
| > > > R5.PARENT_ID P5
| > > > FROM
| > > > RELATION R1,
| > > > RELATION R2,
| > > > RELATION R3,
| > > > RELATION R4,
| > > > RELATION R5
| > > > WHERE
| > > > R1.PARENT_ID=R2.CHILD_ID(+)
| > > > AND R2.PARENT_ID=R3.CHILD_ID(+)
| > > > AND R3.PARENT_ID=R4.CHILD_ID(+)
| > > > AND R4.PARENT_ID=R5.CHILD_ID(+);
| > > >
| > > > This outputs:
| > > > CHILD_ID P1 P2 P3 P4 P5
| > > > 1 2 3 10
| > > > 3 10
| > > > 5 10
| > > > 11 14
| > > > 17 11 14
| > > > 2 3 10
| > > >
| > > > >From the above we can see that:
| > > > CHILD_ID 1 has parents 2, 3, and 10
| > > > CHILD_ID 3 has parents 10
| > > > CHILD_ID 5 has parents 10
| > > > CHILD_ID 11 has parents 14
| > > > CHILD_ID 17 has parents 11, and 14
| > > > CHILD_ID 2 has parents 3, and 10
| > > >
| > > > Using the above, it is easy to determine the levels for the parents.
| > > >
| > > > There might be a CONNECT BY syntax that gives you what you want without
| > > > multiple self joins, and without having to create a program to
| > > > interpret the results of the CONNECT BY clause.
| > > >
| > > > Charles Hooper
| > > > PC Support Specialist
| > > > K&M Machine-Fabricating, Inc.
| > >
| > > That is the essence of the problem- it seems the STARTS WITH clause
| > > can't be used. The problem is seemingly simple, but it is not. People
| > > just don't take the time to read and think, they would rather
| > > criticise. Thanks for your effort.
| >
| > Oracle 9i and above:
| > SELECT
| > PARENT_ID,
| >
| > SUBSTR(SYS_CONNECT_BY_PATH(CHILD_ID,',')||',',2,INSTR(SYS_CONNECT_BY_PATH(CHILD_ID,',')||',',',',1,2)-2)
| > CHILD_ID,
| > LEVEL
| > FROM
| > RELATION
| > CONNECT BY PRIOR
| > PARENT_ID=CHILD_ID;
| >
| > The SYS_CONNECT_BY_PATH option was located by a Google search on the
| > terms: oracle connect by
| >
| > Charles Hooper
| > PC Support Specialist
| > K&M Machine-Fabricating, Inc.
|
| Thanks- that is it!
|

And that is in the first answer I gave you! And that is basic!
Do you see anything complex in there, Mr "I have done SQL for 12 years"?

Regards
Michel Cadot Received on Thu Oct 19 2006 - 10:52:49 CDT