Re: Please help with hierarchical query

On Fri, 09 Jul 2004 12:34:40 -0700, Ronnie wrote:

> 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

select parent_region,child_region
from table
connect by prior parent_region=child_region;

-- 
Well-behaved women seldom make history