Home » SQL & PL/SQL » SQL & PL/SQL » Connect By NoCycle not returning all rows (10g)
Connect By NoCycle not returning all rows [message #413158] Tue, 14 July 2009 11:36 Go to next message
mdeslandes
Messages: 3
Registered: July 2009
Junior Member
Hi,

I'm experimenting a problem with an oracle query using the connect by statement.

Here's how to reproduce my problem.

Create table TestLoop(
parentID numeric(10),
childID numeric(10))
/

insert into TestLoop (parentID, childID) Values (1, 2)
/
insert into TestLoop (parentID, childID) Values (2, 3)
/
insert into TestLoop (parentID, childID) Values (2, 4)
/
insert into TestLoop (parentID, childID) Values (3, 2)
/
insert into TestLoop (parentID, childID) Values (4, 2)
/


I need to be able to return all possible path from a starting point.

My query looks like this :

SELECT parentId, childId, LEVEL AS lvl, sys_connect_by_path(parentId, '/') as root
FROM TestLoop
START WITH parentid = 2
CONNECT BY nocycle parentId = prior childId

When I start with the parentID 2, the retrieve values are good because I can see those relations (2 -> 3 , 3 -> 2, 2 -> 4, 4 -> 2)

Now, if I start from parentID 1 instead, I don't get all relations. I'm not retrieving the (3 -> 2 and 4 -> 2). Can anyone explain me why ? or at least how to retrieve those missing relations.

Thanks in advance !

Re: Connect By NoCycle not returning all rows [message #413162 is a reply to message #413158] Tue, 14 July 2009 11:42 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Re: Connect By NoCycle not returning all rows [message #413291 is a reply to message #413158] Wed, 15 July 2009 02:38 Go to previous messageGo to next message
_jum
Messages: 509
Registered: February 2008
Senior Member
Which ORACLE version do You use ?
There is a bug in 10.2.0.3 see:
http://www.orafaq.com/forum/m/359274/120300/?srch=NOCYCLE#msg_359274
Re: Connect By NoCycle not returning all rows [message #413310 is a reply to message #413291] Wed, 15 July 2009 03:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I have indeed the correct result in 10.2.0.4 and 11.1.0.7.0.

Regards
Michel
Re: Connect By NoCycle not returning all rows [message #413318 is a reply to message #413310] Wed, 15 July 2009 04:03 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
@Michel - what results do you get?
I get these on 10.2.0.4
SQL> SELECT parentId, childId, LEVEL AS lvl, sys_connect_by_path(parentId, '/') as root 
  2  FROM TestLoop
  3  START WITH parentid = 1 
  4  CONNECT BY nocycle parentId = prior childId;

  PARENTID    CHILDID        LVL ROOT
---------- ---------- ---------- -----------------------------------------------------------------
         1          2          1 /1
         2          3          2 /1/2
         2          4          2 /1/2

SQL> SELECT parentId, childId, LEVEL AS lvl, sys_connect_by_path(parentId, '/') as root 
  2  FROM TestLoop
  3  START WITH parentid = 2 
  4  CONNECT BY nocycle parentId = prior childId;

  PARENTID    CHILDID        LVL ROOT
---------- ---------- ---------- -----------------------------------------------------------------
         2          3          1 /2
         3          2          2 /2/3
         2          4          3 /2/3/2
         2          4          1 /2
         4          2          2 /2/4
         2          3          3 /2/4/2

6 rows selected.
, and I can't see why there aren't more results for the first query.
Re: Connect By NoCycle not returning all rows [message #413319 is a reply to message #413158] Wed, 15 July 2009 04:05 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
The fact is that if you intend direct paths (where A to B is not the same as B to A, asymmetric) the query is right but if you intend indirect paths (where A to B is the same as B to A, symmetric) the query should be as follows:

SELECT parentId, childId, 
	LEVEL AS lvl, sys_connect_by_path(parentId, '/')||'/'||childId as root
FROM TestLoop
START WITH parentid = 2
CONNECT BY nocycle parentId = prior childId 
	or childId = prior parentId



Bye Alessandro

[Updated on: Wed, 15 July 2009 04:12]

Report message to a moderator

Re: Connect By NoCycle not returning all rows [message #413374 is a reply to message #413319] Wed, 15 July 2009 07:46 Go to previous messageGo to next message
_jum
Messages: 509
Registered: February 2008
Senior Member
This is imho because there are loops in the data 2->3->2->3..., 2->4->2->4...
You find the loop with:
SELECT     parentid, childid, connect_by_root(parentid) proot, connect_by_root(childid) croot, 
           LEVEL AS lvl,
           SYS_CONNECT_BY_PATH(parentID, '/') AS PATH
      FROM testloop
START WITH (parentid = 1)
CONNECT BY  
 (parentID = prior childid and prior dbms_random.value != 2 and level< = 6  )


PARENTID CHILDID PROOT	CROOT	LVL	PATH

1	2	1	2	1	/1
2	3	1	2	2	/1/2
3	2	1	2	3	/1/2/3
2	3	1	2	4	/1/2/3/2
3	2	1	2	5	/1/2/3/2/3
2	3	1	2	6	/1/2/3/2/3/2
2	4	1	2	6	/1/2/3/2/3/2
2	4	1	2	4	/1/2/3/2
4	2	1	2	5	/1/2/3/2/4
2	3	1	2	6	/1/2/3/2/4/2

[Updated on: Wed, 15 July 2009 07:47]

Report message to a moderator

Re: Connect By NoCycle not returning all rows [message #413375 is a reply to message #413158] Wed, 15 July 2009 07:48 Go to previous messageGo to next message
mdeslandes
Messages: 3
Registered: July 2009
Junior Member
Thanks to all for your reply,

The version of Oracle I use is 10.2.0.1

@Alessandro : Thank you, your query give me the two missing results, but I can't use it since your query don't follow the hierarchy.

In fact I need two query, the first one will start from a child and go back to find all it's parent. The second one will start from a specific parent, and gives me all it's child. In your query, I get all parents and all childs in one query, so this query doesn't really suits my need.

I get the exact same results as JRowbottom...

Any other suggestion would be appreciated.

Thanks again!
Re: Connect By NoCycle not returning all rows [message #413390 is a reply to message #413158] Wed, 15 July 2009 08:28 Go to previous messageGo to next message
mdeslandes
Messages: 3
Registered: July 2009
Junior Member
Thanks jum,

This solve my problem. I've been looking for a solution to this problem for weeks...

But, can you explain what is the utility of "dbms_random.value != 2" in the query ? I don't catch it...

Thanks
Re: Connect By NoCycle not returning all rows [message #413392 is a reply to message #413390] Wed, 15 July 2009 08:38 Go to previous message
_jum
Messages: 509
Registered: February 2008
Senior Member
Fine that this worked for You Smile.
Found the clue here: http://laurentschneider.com/wordpress/2009/05/connect-by-nocycle.html
Previous Topic: how to assign a value for CLOB in Oracle 8i
Next Topic: how to use Case statement in where clause (with respect to Input Params &) (merged)
Goto Forum:
  


Current Time: Thu Dec 08 10:22:30 CST 2016

Total time taken to generate the page: 0.07829 seconds