Solved

Pushing the hierarchy  levels

Posted on 2014-03-19
4
284 Views
Last Modified: 2014-03-25
hI,
i have data which look like below.

Table 1 :

Code   LEAF_node         LEVEL_NUMBER DEPARTMENT

AA      2                     1                001
AA      1                     2                001

B00     2                     2               001
B11     2                     2               001
C11     2                     3               001





step 1)

This step ‘pushes down’ all levels of the hierarchy by one level,
except for level 1, thus opening up level 2 for insertionby the new Fund Focus level.
 The ‘leaf’ node at level 2, however, is left untouched: it is still at level 2.


Code   LEAF_node         LEVEL_NUMBER DEPARTMENT

AA      2                     1                001
AA      1                     2                001

B00     3                     3               001
B11     3                     3               001
C11     3                     4               001


step 2)

Insert two rows into the step 1) table using FUND_FOCUS

FUND_FOCUS
cODE  DEPARTMENT
TT      001
TT1     001

Result should be:

Code   LEAF_node         LEVEL_NUMBER DEPARTMENT

AA      2                     1                001
AA      1                     2                001
TT      2                     2                001
TT1     2                     2                001
B00     3                     3                001
B11     3                     3               001
C11     3                     4               001


There is step 3) but we can discuss that later.

Thanks
0
Comment
Question by:sam2929
  • 2
4 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 39942086
What's special about the AA records that they don't change?

How are we supposed to know where this insertion happens?
0
 

Author Comment

by:sam2929
ID: 39942327
AA records are level 1 records insert will happen at level 2 records all other are pushed down
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39942369
AA records are level 1 records insert will happen at level 2 records all other are pushed down

only one of the AA records is level 1

How do we know that inserts will happen at level 2?  Is that an input somehow or are we supposed to derive that from the data in some way.  If so, how?

Does level 1 remain untouched because level 1 is special or because level 1 is higher than level 2?
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 39942488
you should rethink your table design, by reading this excellent article about hierarchies:
http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/
it relates specifically to mysql, so not all SQL will match for oracle, but the storing of the data is clearly ligned out
your "request" to store the "level" information in the table is violating normalization rules, btw.
in oracle, you can query hierarchical data with ID/PARENTID information only by using CONNECT BY + START WITH syntax, explained here:
http://docs.oracle.com/cd/B19306_01/server.102/b14200/queries003.htm
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now