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