Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE objective ( id, parent_id, label, cost ) AS
SELECT 1, NULL, 'A', NULL FROM DUAL UNION ALL
SELECT 2, 1, 'B', 1 FROM DUAL UNION ALL
SELECT 3, 2, 'C', 2 FROM DUAL UNION ALL
SELECT 4, 1, 'D', NULL FROM DUAL UNION ALL
SELECT 5, 4, 'E', 3 FROM DUAL UNION ALL
SELECT 6, 5, 'F', 4 FROM DUAL UNION ALL
SELECT 7, 4, 'G', 5 FROM DUAL;
7 rows affected

SELECT id,
       parent_id,
       label,
       COALESCE(PRIOR cost, 0) + COALESCE(cost, 0) AS cost
FROM   objective
START WITH
       parent_id IS NULL
CONNECT BY
       PRIOR id = parent_id
ORDER SIBLINGS BY
       id;


ID
PARENT_ID
LABEL
COST


1
null
A
0

2
1
B
1

3
2
C
3

4
1
D
0

5
4
E
3

6
5
F
7

7
4
G
5


SELECT id,
       parent_id,
       label,
       ( SELECT COALESCE(SUM(cost), 0)
         FROM   objective c
         START WITH c.id = o.id
         CONNECT BY PRIOR parent_id = id -- Reverse the direction
       ) AS cost
FROM   objective o
START WITH
       parent_id IS NULL
CONNECT BY
       PRIOR id = parent_id
ORDER SIBLINGS BY
       id;


ID
PARENT_ID
LABEL
COST


1
null
A
0

2
1
B
1

3
2
C
3

4
1
D
0

5
4
E
3

6
5
F
7

7
4
G
5


WITH rsqfc (id, parent_id, label, cost) AS (
  SELECT id, parent_id, label, COALESCE(cost, 0)
  FROM   objective
  WHERE  parent_id IS NULL
UNION ALL
  SELECT o.id, o.parent_id, o.label, COALESCE(o.cost, 0) + r.cost
  FROM   objective o
         INNER JOIN rsqfc r
         ON (r.id = o.parent_id)
) SEARCH DEPTH FIRST BY id SET order_id
SELECT *
FROM   rsqfc;


ID
PARENT_ID
LABEL
COST
ORDER_ID


1
null
A
0
1

2
1
B
1
2

3
2
C
3
3

4
1
D
0
4

5
4
E
3
5

6
5
F
7
6

7
4
G
5
7


UPDATE objective
SET   cost = 8
WHERE id = 4;
1 rows affected

-- This will have the incorrect cost for #6 as it only totals
-- the cost for the leaf and its parent.
SELECT id,
       parent_id,
       label,
       COALESCE(PRIOR cost, 0) + COALESCE(cost, 0) AS cost
FROM   objective
START WITH
       parent_id IS NULL
CONNECT BY
       PRIOR id = parent_id
ORDER SIBLINGS BY
       id;


ID
PARENT_ID
LABEL
COST


1
null
A
0

2
1
B
1

3
2
C
3

4
1
D
8

5
4
E
11

6
5
F
7

7
4
G
13


SELECT id,
       parent_id,
       label,
       ( SELECT COALESCE(SUM(cost), 0)
         FROM   objective c
         START WITH c.id = o.id
         CONNECT BY PRIOR parent_id = id -- Reverse the direction
       ) AS cost
FROM   objective o
START WITH
       parent_id IS NULL
CONNECT BY
       PRIOR id = parent_id
ORDER SIBLINGS BY
       id;


ID
PARENT_ID
LABEL
COST


1
null
A
0

2
1
B
1

3
2
C
3

4
1
D
8

5
4
E
11

6
5
F
15

7
4
G
13


WITH rsqfc (id, parent_id, label, cost) AS (
  SELECT id, parent_id, label, COALESCE(cost, 0)
  FROM   objective
  WHERE  parent_id IS NULL
UNION ALL
  SELECT o.id, o.parent_id, o.label, COALESCE(o.cost, 0) + r.cost
  FROM   objective o
         INNER JOIN rsqfc r
         ON (r.id = o.parent_id)
) SEARCH DEPTH FIRST BY id SET order_id
SELECT *
FROM   rsqfc;


ID
PARENT_ID
LABEL
COST
ORDER_ID


1
null
A
0
1

2
1
B
1
2

3
2
C
3
3

4
1
D
8
4

5
4
E
11
5

6
5
F
15
6

7
4
G
13
7