Re: hierarchical queries with connect by prior
On Thu, May 06, 1999 at 04:59:16PM -0400, Art S. Kagel wrote:
> Sven Heins wrote:
> >
> > Hi there,
> >
> > is there something similar in informix to the oracle expression "connect by
> > prior". With that statement you can build hierarchical queries to find a
> > child-parent-relationship in one table.
> >
> > CREATE TABLE TEST
> > (
> > MAIN_ID integer,> > PARENT_ID integer
> > );
> >
> > select main_id
> > from test> > start with main_id = 1
> > connect by prior main_id = parent_id;
> >
> > The result will be the MAIN_ID "1" and all rows that have the PARENT_ID "1"
> > and even their children.
> >
> > Can informix do this??? If yes, what is the syntax?
>
> No this is an Oracle specific extension to the SQL language that is not
> supported by Informix (or any other DB I am aware of).
>
> Art S. Kagel
DB2 supports 'recursive' queries too. It's a bit more complex
than Oracle's in coding but it could be done.
Best Regards,
Octav
--
Octav Chiriac Phone: (373) 2 21 20 96
NetInfo S.R.L. Fax: (373) 2 21 36 59
Chisinau (373) 2 24 00 83
Moldova, Republic of mailto:com@netinfo-moldova.com