Please help with a Hierarchical Query

[ronnie]

Hi,

I have a table with sample records as below

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

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

[salmanucit]

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

[padders]

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)

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>

[Axr2]

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!

[ronnie]

Originally posted by padders

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

[gandolf989]

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.