-
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
Last edited by ronnie; 07-09-2004 at 03:35 PM.
Ronnie
ronnie_yours@yahoo.com
You can if you think you can.
-
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
Salman Ahmed Qureshi
Lahore, Pakistan
-
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!
-
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
Ronnie
ronnie_yours@yahoo.com
You can if you think you can.
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|