Please help with a Hierarchical Query

Hi,

I have a table with sample records as below

Code:

Parent Region Child Region ============= =========== North America USA USA CALIFORNIA USA CONNECTICUT CALIFORNIA ORANGE COUNTY CONNECTICUT FAIRFIRLD COUNTY FAIRFIELD COUNTY NORWALK

I want to create a flattened table / materialized view such as below

Code:

Parent Region Child Region ============= =========== North America USA North America CALIFORNIA North America CONNECTICUT North America ORANGE COUNTY North America FAIRFIELD COUNTY North America NORWALK USA CALIFORNIA USA CONNECTICUT USA ORANGE COUNTY USA FAIRLFIELD COUNTY USA NORWALK CALIFORNIA ORANGE COUNTY CONNECTICUT FAIRFIELD COUNTY CONNECTICUT NORWALK FAIRFIELD COUNTY NORWALK

I am at loss as to how do I write a query to do this. I can do it by writing a stored procedure but was thinking whether its poccible using connect by in a single query to achieve this.

Thanks
Ron

hi
tell me does it work

select parentregion,childregion from TABLE group by parentregion,childregion;

same query i applied on EMP table on MGR and EMPNO columns because these columns have the same situation as u have described

Regards

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>

padders - awesome stuff! Didn't know about the sys_connect_by_path deal. Sweet. Spent a good couple of hours on this stuff to no avail yesterday!

Quote:

Originally posted by padders

Code:

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>

I tried executing the above query on 8.1.7 and it gives me an error saying "Invalid column name" for SYS_CONNECT_BY_PATH

Thanks
Ron

Quote:

Originally posted by ronnie
I tried executing the above query on 8.1.7 and it gives me an error saying "Invalid column name" for SYS_CONNECT_BY_PATH

Thanks
Ron

SYS_CONNECT_BY_PATH is new to 9i. Hence it won't work in 8i.