Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Going up the tree

2 posts in General Discussion Last posting was on 2003-02-26 22:14:25.0Z
tester Posted on 2003-02-26 21:43:24.0Z
From: "tester" <tester@hotmail.com>
Subject: Going up the tree
Date: Wed, 26 Feb 2003 16:43:24 -0500
Lines: 15
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4522.1200
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200
Message-ID: <nkzeaLe3CHA.187@forums.sybase.com>
Newsgroups: ianywhere.public.general
NNTP-Posting-Host: cefdev1465.unicef.org 57.69.14.65
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub ianywhere.public.general:780
Article PK: 2729

HI,

Is there a clause in ASA8 whereby we can traverse a tree
i.e if i give a the topmost id id should return to me all the next level
children and for each of the next level children it should return its
children .

Whati am looking is smething similiar to connect by clause
of ORACLE.

Any help will be highly appreciated.


Glenn Paulley Posted on 2003-02-26 22:14:25.0Z
Date: Wed, 26 Feb 2003 17:14:25 -0500
From: Glenn Paulley <paulley@ianywhere.com>
Reply-To: paulley@ianywhere.com
Organization: iAnywhere Solutions
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.2.1) Gecko/20021130
X-Accept-Language: en-us, en
MIME-Version: 1.0
Subject: Re: Going up the tree
References: <nkzeaLe3CHA.187@forums.sybase.com>
In-Reply-To: <nkzeaLe3CHA.187@forums.sybase.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
Message-ID: <YQw7zce3CHA.182@forums.sybase.com>
Newsgroups: ianywhere.public.general
Lines: 31
NNTP-Posting-Host: 172.31.141.96
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub ianywhere.public.general:781
Article PK: 2731


tester wrote:
> HI,
>
> Is there a clause in ASA8 whereby we can traverse a tree
> i.e if i give a the topmost id id should return to me all the next level
> children and for each of the next level children it should return its
> children .
>
> Whati am looking is smething similiar to connect by clause
> of ORACLE.
>
> Any help will be highly appreciated.
>
>
>
>

The ANSI SQL 99 implementation of Oracle's CONNECT BY is RECURSIVE
UNION. This feature will be a part of the next major release of SQL
Anywhere, code-named "Banff".

In ASA 8 and below, you can accomplish the same thing by doing
self-joins of the table to itself, with the number of self-joins equal
to the number of "levels" you wish to traverse. Alas, without RECURSIVE
UNION it is impossible to generate a result set that handles a *dynamic*
number of "levels" in the tree in a single SQL statement. Another
alternative is to code the query as a procedure.

--
Glenn Paulley
Research and Development Manager, Query Processing
iAnywhere Solutions Engineering

Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
Developer Community at http://www.ianywhere.com/developer