hierarchical queries with connect by prior

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 teststart 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? Thanks for your help. Sven -- Sven Heins solution42 IT systems & consulting Gmbh & Co KG http://www.solution42.de

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

Sven Heins (Heins.Sven@solution42.de) wrote: : 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. There's an entirely different approach to the whole problem that's faster than either the Oracle or the DB2 approach, but it requires the IDS/UD engine. http://www.informix.com/informix/grants/sharewarecoop/bladelets/hierarchical_bladelet.htm