Home » SQL & PL/SQL » SQL & PL/SQL » Writing Hierarchical data into another table (Windows)
icon4.gif  Writing Hierarchical data into another table [message #389292] Sat, 28 February 2009 03:03 Go to next message
ap23639
Messages: 1
Registered: February 2009
Junior Member
Hi all,

I need some help regarding a problem I am currently facing.
There are two tables.
in one table hierarchical data is there in parent & child mode.
e.g A1 B1
B1 C1
C1 C2
C2 C3
C3 C4
C4 C5
In another table transaction data is there

e.g A1 CC 200
C1 XX 500
C4 YY 600

now my requirement is to write all missing transaction data.

The data in above tables can be written as below:

A1(200)-->B1(novalue)-->C1(500)-->C2(novalue)-->C3(novalue)-->C4(600)--->C5(novalue).



while walking from top level (say A1) to the bottom one (C5)
I have to replicate the missing node value with the immidate top value.E.g. value of A1 is 200 but the value at node B1 is missing.So it should fill the node value by 200.As there is a value at C1 node overwritting of value(here its 200) should be stopped.
At C1 value is 500,but the immidate nodes (e.g. C2 & C3) has no value.so it should be over written by 500 and it should stop at C4.Again by applying the same logic,the value at node C5 should be written as 600.

So I have write a query/procedure to insert the missing hierarchical data with missing node values.

It should look like this

B1 200
C2 500
C3 500
C5 600

So the task is to write all the missing hierarchical data
into a third table.

Please let me know if any further clarification is required.

Thanks
Abhilash




Re: Writing Hierarchical data into another table [message #389293 is a reply to message #389292] Sat, 28 February 2009 03:11 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Post a Test case: create table and insert statements along with the result you want with these data.

Use SQL*Plus and copy and paste what you already tried.

Regards
Michel
Previous Topic: Greatest Function
Next Topic: How to show oracle views data into Microsoft Exel Sheet (merged)
Goto Forum:
  


Current Time: Mon Dec 05 15:11:55 CST 2016

Total time taken to generate the page: 0.08566 seconds