Solved

Pushing the hierarchy  levels

Posted on 2014-03-19
4
286 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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
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…
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 copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

813 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

12 Experts available now in Live!

Get 1:1 Help Now