Home » Other » Test » Understanding connect by prior (10.2.0.1.0, WIN XP)
Understanding connect by prior [message #326063] Tue, 10 June 2008 01:51 Go to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
Hi,

i am trying to understand CONNECT BY PRIOR and SYS_CONNECT_BY_PATH form TOM Kyte' article

Here is the table being worked upon.

SQL> select * from test_Table;

         A          B
---------- ----------
         1         -1
         2          1
         3          1
         4          2
         5          2
         6          4
         7          4
         8          5
         9          5
        10          3
        11          3

11 rows selected.


After using this select statement

SQL> SELECT a
  2    FROM test_table
  3    CONNECT BY PRIOR b=a
  4  /


The answer looks like this.

SQL> select a from test_Table connect by prior b=a;

         A
----------
         1
         2
         1
         3
         1
         4
         2
         1
         5
         2
         1

         A
----------
         6
         4
         2
         1
         7
         4
         2
         1
         8
         5
         2

         A
----------
         1
         9
         5
         2
         1
        10
         3
         1
        11
         3
         1


Quote:
33 rows selected.


How are the two tables being used? How did the final answer arrive?
Re: Understanding connect by prior [message #326070 is a reply to message #326063] Tue, 10 June 2008 02:05 Go to previous messageGo to next message
Michel Cadot
Messages: 59143
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which 2 tables? There is only one!
Add LEVEL to your select to see the depth of your row.
Read:
Hierarchical Queries
Hierarchical Query Pseudocolumns
Hierarchical Query Operators
Hierarchical Function

Regards
Michel
Re: Understanding connect by prior [message #326133 is a reply to message #326063] Tue, 10 June 2008 05:11 Go to previous messageGo to next message
Frank
Messages: 7877
Registered: March 2000
Senior Member
The test-forum is not meant for asking questions. It is there to test how to post.
Re: Understanding connect by prior [message #326153 is a reply to message #326133] Tue, 10 June 2008 06:26 Go to previous message
Michel Cadot
Messages: 59143
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry to have answered this, I thought it was Homework, Training & Certification forum.

I move it to this forum as even if OP just wanted to test his post this is a question that may interest others.

Regards
Michel
Previous Topic: how to make a url
Next Topic: testing
Goto Forum:
  


Current Time: Fri Sep 19 19:33:07 CDT 2014

Total time taken to generate the page: 1.14663 seconds