Please help with a Hierarchical Query
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Please help with a Hierarchical Query

  1. #1
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577

    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.

  2. #2
    Join Date
    Jul 2004
    Location
    Pakistan
    Posts
    46
    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

  3. #3
    Join Date
    Jan 2004
    Posts
    162
    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>

  4. #4
    Join Date
    Oct 2002
    Posts
    807
    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!

  5. #5
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    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.

  6. #6
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,999
    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
  •  



Click Here to Expand Forum to Full Width