Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Pushing the hierarchy  levels

Posted on 2014-03-19
4
Medium Priority
?
292 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 1500 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

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
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…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

604 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