| finding missing parent from oracle hierarchical query [message #611587] |
Fri, 04 April 2014 01:33  |
 |
ashwanth77
Messages: 95
Registered: April 2013
Location: India
|
Member |
|
|
i have a table with parent/child relationship. how do i find the missing parent
hierachiacl table
child parent
2000698835 2001455376
if the parent is not present in the customer table i need to insert it to customer table.
here how do i find the missing parent in customer table.
select * from customer where true_gcdb_source_key='2001455376'(which is the parent)
what i tried so far
select level, PARENT_GCDB_ID
from EU_SAP_CUST_REL_INIT
where level <=4
start with PARENT_GCDB_ID not in (
select true_gcdb_source_key
from customer
where true_gcdb_source_key=PARENT_GCDB_ID
)
connect by prior PARENT_GCDB_ID=CHILD_GCDB_ID
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: finding missing parent from oracle hierarchical query [message #611617 is a reply to message #611614] |
Fri, 04 April 2014 08:25  |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
So write INSERT statement:
insert into customer( <column list of customer table> )
with your_query as ( <the query you posted> )
, parents as ( select distinct <column with parent> parent
from your_query
where <column with parent> is not null )
select <value expression list (one of the expressions should be parent)>
from parents;
Substitute text inside <> with your actual values (you did not post anything except the query).
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
