i have a table objective with columns id, label, cost, parent_id and i have this query

select 
b.id,
b.parent_id,
b.label,
b.cost
from objective b
start with b.parent_id is null
connect by 
prior b.id=b.parent_id
ORDER SIBLINGS BY b.id  

only a leaf (and sometimes a parent of a leaf) has the column cost not null the others are almost always null, is it possible to get the cost of every node (summing the cost of the nodes right below) or not.

i know it can be done with join and sum but i thought maybe there is a simpler way to do it. if this was the table content :

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

the result should be something like this :

ID  PARENT_ID   LABEL   COST    CALCULATED_COST
1   null        A       0       15(meaning 0 is a wrong value) 
2   1           B       1       3 (meaning 1 is a wrong value)
3   2           C       3       3
4   1           D       0       12
5   4           E       3       7 (meaning 3 is a wrong value)
6   5           F       7       7
7   4           G       5       5

CodePudding user response：

only a leaf (and sometimes a parent of a leaf) has the column cost not null the others are almost always null

If the rule is that "only a leaf or a parent of a leaf have a non-null cost and the others are always null" then you can use:

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

If the rule is that "any row can have a non-null cost" then you will need to parse the data structure in both directions:

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

or use a recursive sub-query factoring clause:

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;

fiddle

Update

If you are looking to sum the cost for a row and all its descendants then you can use:

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

Which, for the sample data outputs:

CREATE TABLE objective ( id, parent_id, label, cost ) AS
SELECT 1, NULL, 'A', 0 FROM DUAL UNION ALL
SELECT 2, 1, 'B', 1 FROM DUAL UNION ALL
SELECT 3, 2, 'C', 3 FROM DUAL UNION ALL
SELECT 4, 1, 'D', 0 FROM DUAL UNION ALL
SELECT 5, 4, 'E', 3 FROM DUAL UNION ALL
SELECT 6, 5, 'F', 7 FROM DUAL UNION ALL
SELECT 7, 4, 'G', 5 FROM DUAL;

Outputs:

fiddle

Update 2:

If you only want to total the descendants that are leaves then:

SELECT id,
       parent_id,
       label,
       cost,
       ( SELECT COALESCE(SUM(cost), 0)
         FROM   objective c
         WHERE  CONNECT_BY_ISLEAF = 1
         START WITH c.id = o.id
         CONNECT BY PRIOR id = parent_id
       ) AS total_cost
FROM   objective o
START WITH
       parent_id IS NULL
CONNECT BY
       PRIOR id = parent_id
ORDER SIBLINGS BY
       id;

Which, for the sample data, outputs:

fiddle