Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: writing "recursive SQL"
check out the "connect by B = prior A start with A = <start value>" syntax.
this enables you to do a tree walk that will automatically stop when there
are no more child rows.
Kind regards,
Lex.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of susan lam
Sent: Thursday, September 09, 2004 20:27
To: oracle-l_at_freelists.org
Subject: writing "recursive SQL"
Hi,
I would like to write a SQL to recursively extract
data from a table.
The algorithm is as follows:
SQL> select * from mytable;
A B
---------- ----------
1 2 2 3 3 4 4 5 5 6 0 7 7 8 8 9
8 rows selected.
If A=3, output the corresponding value of B (ie 4), then match B (ie 4) with A and output the next corresponding value of B (ie 5) and so on... Recursion stops when B != A
A & B are unqiue and A=<value> is a user input value
The result I'm looking for is:
if A=3, then the output is:
4
5
6
if A=7, then the output is:
8
9
Is there a way to accomplish that in SQL?
thanks.
susan
--
To unsubscribe - mailto:oracle-l-request_at_freelists.org&subject=unsubscribe
To search the archives - http://www.freelists.org/archives/oracle-l/
Received on Thu Sep 09 2004 - 14:32:57 CDT