Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 295
  • Last Modified:

Pushing the hierarchy levels

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
sam2929
Asked:
sam2929
  • 2
1 Solution
 
sdstuberCommented:
What's special about the AA records that they don't change?

How are we supposed to know where this insertion happens?
0
 
sam2929Author Commented:
AA records are level 1 records insert will happen at level 2 records all other are pushed down
0
 
sdstuberCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now