OraFAQ Forum: SQL & PL/SQL » Oracle 10g : Connect by prior

Home » SQL & PL/SQL » SQL & PL/SQL » Oracle 10g : Connect by prior

Tue, 08 April 2014 02:59

ashwanth77
Messages: 95
Registered: April 2013
Location: India

Member

what is difference between using PRIOR in:

CONNECT BY CUST_ID= PRIOR CUST_CUST_ID

and:

CONNECT BY PRIOR CUST_ID= CUST_CUST_ID

Tue, 08 April 2014 03:13

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

One travels the hierarchy from top to bottom and the other one from bottom to top.

Tue, 08 April 2014 04:28

ashwanth77
Messages: 95
Registered: April 2013
Location: India

Member

what is difference between using level i get the same result

SELECT cust_id AS child_id
  FROM CUSTOMER
 WHERE LEVEL <= 8
 START WITH cust_id = 1929
CONNECT BY   cust_id =  PRIOR cust_cust_id

o/p
child_id
1929
532
96
1212
0
2550

without using level

SELECT cust_id AS child_id
  FROM CUSTOMER
START WITH cust_id = 1929
CONNECT BY   cust_id =  PRIOR cust_cust_id

o/p
child_id
1929
532
96
1212
0
2550

Tue, 08 April 2014 04:38

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You do not reach a level greater than 8 so there is no difference.
It is the same thing than "rownum<100" if you have only 10 rows.

Previous Topic:	Manipulating XML Clob
Next Topic:	How to get last two max values

Goto Forum:

]

Current Time: Fri Apr 26 23:06:06 CDT 2024