>I wonder if this can be done. I have a hierarchy, which needs to update calculated values to their parents.
>
>Level | ID | Parent_ID | Percent
>1 100 0 0
>2 101 100 0
>3 102 101 0
>2 103 100 0
>1 104 0 0
>
>Now I update percent of id 102:
>
>lnID = 102
>lnParent_Id = 101
>Update Levels Set Percent = 10 WHERE id = m.lnID
>
>Now I need to recalculate the parent's percentages:
>
>UPDATE Levels ;
> SET Percent = (SELECT SUM(NVL(Percent,0)) / COUNT(*) ;
> FROM Levels Tmp ;
> WITH (BUFFERING = .T.) ;
> WHERE Tmp.Parent_ID = m.lnParent_Id) ;
> WHERE Levels.Id = m.lnParent_Id
>
>So the result is:
>
>Level | ID | Parent_ID | Percent
>1 100 0 0
>2 101 100 10
>3 102 101 10
>2 103 100 0
>1 104 0 0
>
>What is missing is the update for the grandparents and further (right now there is no limitation), which should then result in the following values:
>
>Level | ID | Parent_ID | Percent
>1 100 0 5
>2 101 100 10
>3 102 101 10
>2 103 100 0
>1 104 0 0
>
>I wonder if that can be done without manually looping through the table?
See whether anything here gives you ideas:
http://www.tomorrowssolutionsllc.com/Articles/Handling%20Hierarchical%20Data.pdfTamar
