Home » SQL & PL/SQL » SQL & PL/SQL » Hierarchical queries (Oracle 10g)
Hierarchical queries [message #381435] Fri, 16 January 2009 23:33 Go to next message
soumyasmita
Messages: 3
Registered: January 2009
Location: Mumbai
Junior Member

Hi,
I have a table with the below structure:
child_id parent_id type        NAME
100	 200	    zip	       tHANE
200      300	    cITY       mUMBAI
300      400	   State       Maharashtra    
400      500      COUNTRY      India

I need the data in the below form from the table to be stored in a materailezed view:
child_id  parent_country_id childname       parent_cntry_name
100       400	             Thane          India
200       400                Mumbai         India
300       400                Maharashtra    India

I am not concerned about the top record of type country.

Can u please guide me on how to achieve this using a query to populate into a materailzed view? Also please note that I need to do this for all the records in the table, which is a huge table.Also the table holds various zip to country hierrachies and for each hierarchy i need to display the child and top most parent(country).

thanks in advance!

[Mod-Edit: Frank added [code]-tags to improve readability]

[Updated on: Sat, 17 January 2009 00:45] by Moderator

Report message to a moderator

Re: Hierarchical queries [message #381436 is a reply to message #381435] Fri, 16 January 2009 23:37 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you.

I don't understand the question or what answer you desire.
Re: Hierarchical queries [message #381441 is a reply to message #381436] Sat, 17 January 2009 00:58 Go to previous messageGo to next message
soumyasmita
Messages: 3
Registered: January 2009
Location: Mumbai
Junior Member

Hi, I need a query for creation of a Materialzed view to achieve the data to be displayed as in the post above.

The hierarchical table needs to be converted to a flat table, with child and top msot parent.

I jsut provided some sample data, do u want the exact table snapshot?

Am using Oracle 10g
Re: Hierarchical queries [message #381448 is a reply to message #381435] Sat, 17 January 2009 01:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a Test case: create table and insert statements along with the result you want with these data.
Post what you already tried and why it does not fit your requirements.

Regards
Michel
Re: Hierarchical queries [message #381474 is a reply to message #381448] Sat, 17 January 2009 02:15 Go to previous message
soumyasmita
Messages: 3
Registered: January 2009
Location: Mumbai
Junior Member

My table CX_GEO_DATA_TEMP is a hierarchical table, having region hierarchies. Eg. zip, city, state, country a record of type zip will point to its parent of type city. City will point to state, state to country and so on. I have provided the current form of data and required form of data in my 1st post.

Please refer attachment for all details on create script for a similar table, insert statements and queries used.
Previous Topic: Flashback Query
Next Topic: Anonymous Blocks
Goto Forum:
  


Current Time: Sun Dec 11 08:29:02 CST 2016

Total time taken to generate the page: 0.07950 seconds