Hierarchies

Hi all, I have an SQL problem that I have run into and I am wondering if anybody has any solutions to it. The problem is the ordering of a hierarchy on a report. I have two table one of which contains the parent and child relationships between entries in the other table. Eg table1 entry table2 parent-entry child-entry I need to produce an ordered lists starting from the Top of the Hierarchy in the following orders:- Report 1 Report 2 entry parent child Top Top A A Top B 1 Top C 1A A 1 1B A 2 1C B 5 2 C 4 2A 1 1A 2B 1 1B B 1 1C 5 2 2A 5A 2 2B 5B 4 4A C 5 5A 4 5 5B 4A Now I have an example from an Oracle (Whoops! Sorry, I hope mentioning that word didnt cause any problems for anybody) database which uses a special function. Eg: select LPAD(' ',2*LEVEL) || child-entry MODEL from table2 connect by prior child-entry = parent-entry start with parent-entry = "Top" This probably isn't correct syntax as I am borrowing the idea from a working program and I dont know Oracle. But this produced a report ordered along the lines of the Report 1 above. Unfortunately this is not possible in either ANSI Standard or Informix SQL as far as I know. Does anybody have any Informix code that can produce this ordering or have any suggestions on how I might code the select statements. Cheers - Jim -------------------------------------------------------------------- Name: Jim Gordon Internet: jgordon@ssf-sys.DHL.COM Company: DHL Systems Inc Phone: (415) 358-5911 (Work) Address: 1700 S. Amphlett Blvd. (415) 882-9728 (Home) San Mateo, CA 94402 Fax: (415) 571-6429 --------------------------------------------------------------------