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.

-------------------------------
visit http://www.naturaljoin.nl <http://www.naturaljoin.nl>
-------------------------------
skype me <callto://lexdehaan>


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of susan lam
Sent: Thursday, September 09, 2004 20:27
To: oracle-l@xxxxxxxxxxxxx
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



__________________________________
Do you Yahoo!?
Yahoo! Mail - You care about security. So do we.
http://promotions.yahoo.com/new_mail
--
To unsubscribe - mailto:oracle-l-request@xxxxxxxxxxxxx&subject=unsubscribe
To search the archives - http://www.freelists.org/archives/oracle-l/


-- Binary/unsupported file stripped by Ecartis --
-- Type: text/x-vcard
-- File: Lex de Haan.vcf


--
To unsubscribe - mailto:oracle-l-request@xxxxxxxxxxxxx&subject=unsubscribe 
To search the archives - http://www.freelists.org/archives/oracle-l/

Other related posts: