Getting the root node is trivial in 10g with CONNECT_BY_ROOT operator. In 9i you can get (albeit less elegantly) to the root node by using SYS_CONNECT_BY_PATH and stripping off everything but the first node - see examples of both below...(this is the bit where you say you have 7.1.6)
Code:Personal Oracle Database 10g Release 10.1.0.2.0 - Production With the Partitioning, OLAP and Data Mining options SCOTT> CREATE TABLE table_name ( 2 parent_region VARCHAR2 (30), 3 child_region VARCHAR2 (30)); Table created. SCOTT> INSERT INTO table_name VALUES ('North America', 'USA'); 1 row created. SCOTT> INSERT INTO table_name VALUES ('USA', 'CALIFORNIA'); 1 row created. SCOTT> INSERT INTO table_name VALUES ('USA', 'CONNECTICUT'); 1 row created. SCOTT> INSERT INTO table_name VALUES ('CALIFORNIA', 'ORANGE COUNTY'); 1 row created. SCOTT> INSERT INTO table_name VALUES ('CONNECTICUT', 'FAIRFIELD COUNTY'); 1 row created. SCOTT> INSERT INTO table_name VALUES ('FAIRFIELD COUNTY', 'NORWALK'); 1 row created. SCOTT> COMMIT; Commit complete. SCOTT> SELECT CONNECT_BY_ROOT (parent_region) parent_region, 2 child_region 3 FROM table_name 4 CONNECT BY parent_region = PRIOR child_region; PARENT_REGION CHILD_REGION ------------------------------ ------------------------------ CALIFORNIA ORANGE COUNTY CONNECTICUT FAIRFIELD COUNTY CONNECTICUT NORWALK FAIRFIELD COUNTY NORWALK North America USA North America CALIFORNIA North America ORANGE COUNTY North America CONNECTICUT North America FAIRFIELD COUNTY North America NORWALK USA CALIFORNIA USA ORANGE COUNTY USA CONNECTICUT USA FAIRFIELD COUNTY USA NORWALK 15 rows selected. SCOTT> SELECT SUBSTR (parent_region, 2, 2 INSTR (parent_region, '#', 2) - 2) parent_region, 3 child_region 4 FROM (SELECT SYS_CONNECT_BY_PATH ( 5 parent_region, '#') || '#' parent_region, 6 child_region 7 FROM table_name 8 CONNECT BY parent_region = PRIOR child_region); PARENT_REGION CHILD_REGION ------------------------------ ------------------------------ CALIFORNIA ORANGE COUNTY CONNECTICUT FAIRFIELD COUNTY CONNECTICUT NORWALK FAIRFIELD COUNTY NORWALK North America USA North America CALIFORNIA North America ORANGE COUNTY North America CONNECTICUT North America FAIRFIELD COUNTY North America NORWALK USA CALIFORNIA USA ORANGE COUNTY USA CONNECTICUT USA FAIRFIELD COUNTY USA NORWALK 15 rows selected. SCOTT>




Reply With Quote