Solved

Pushing the hierarchy  levels

Posted on 2014-03-19
4
290 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 74

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 74

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 143

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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

688 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