Equivalent of connect by

i have a sample code in Oracle... that use connect by. wonder what's the equivalent in Informix and do i need to change the codes. code as follow: 1 select itinerary_id, dept_station,arrv_station 2 from 3 (select rownum-level itinerary_id, 4 dept_station,arrv_station 5 from yoren_tmp 6 where dept_station != '&&to' 7 start with dept_station = '&&from' 8 connect by prior arrv_station = dept_station 9 ) a 10 where exists 11 (select * from 12 (select rownum-level itinerary_id, 13 dept_station,arrv_station 14 from yoren_tmp 15 where dept_station != '&&to' 16 start with dept_station = '&&from' 17 connect by prior arrv_station = dept_station 18 ) b 19 where a.itinerary_id = b.itinerary_id and 20* b.arrv_station = '&&to') SQL>/ SQL> / Enter value for to: D old 6: where dept_station != '&&to' new 6: where dept_station != 'D' Enter value for from: A old 7: start with dept_station = '&&from' new 7: start with dept_station = 'A' old 15: where dept_station != '&&to' new 15: where dept_station != 'D' old 16: start with dept_station = '&&from' new 16: start with dept_station = 'A' old 20: b.arrv_station = '&&to') new 20: b.arrv_station = 'D') -----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

slok00@yahoo.com wrote: : i have a sample code in Oracle... that use connect by. : wonder what's the equivalent in Informix and do i need to : change the codes. Informix has no counterpart to "connect by". You've got to code the logic manually Joe : code as follow: : 1 select itinerary_id, dept_station,arrv_station : 2 from : 3 (select rownum-level itinerary_id, : 4 dept_station,arrv_station : 5 from yoren_tmp : 6 where dept_station != '&&to' : 7 start with dept_station = '&&from' : 8 connect by prior arrv_station = dept_station : 9 ) a : 10 where exists : 11 (select * from : 12 (select rownum-level itinerary_id, : 13 dept_station,arrv_station : 14 from yoren_tmp : 15 where dept_station != '&&to' : 16 start with dept_station = '&&from' : 17 connect by prior arrv_station = dept_station : 18 ) b : 19 where a.itinerary_id = b.itinerary_id and : 20* b.arrv_station = '&&to') : SQL>/ : SQL> / : Enter value for to: D : old 6: where dept_station != '&&to' : new 6: where dept_station != 'D' : Enter value for from: A : old 7: start with dept_station = '&&from' : new 7: start with dept_station = 'A' : old 15: where dept_station != '&&to' : new 15: where dept_station != 'D' : old 16: start with dept_station = '&&from' : new 16: start with dept_station = 'A' : old 20: b.arrv_station = '&&to') : new 20: b.arrv_station = 'D') : -----------== Posted via Deja News, The Discussion Network ==---------- : http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own -- --------------------------------------------------------------------------- Joe Lumbley(jlumbley@netcom.com) author of: "INFORMIX DBA Survival Guide" The DBA Survival Guide, Second Edition will cover NT, 7.X through 7.3, graphical utilities, more survival hints. Publish date: 12/16/98 FINALLY! ---------------------------------------------------------------------------