Sum distance along with SYS_CONNECT_BY_PATH

Question

Taking reference with this question on SO, Input graph Table is -

P_FROM      P_TO     DISTANCE
A           B         4
A           C         7
B           C        10
C           D        15
B           D        17
A           D        23
B           E        22
C           E        29

The expected answer is -

P_FROM    P_TO     FULL_ROUTE     TOTAL_DISTANCE
A         E        A->B->E        26
A         E        A->C->E        36
A         E        A->B->C->E     43

The query given in the answer is successfully retrieving the result -

WITH multiroutes (p_from, p_to, full_route, total_distance)
        AS (SELECT p_from,
                   p_to,
                   p_from || '->' || p_to full_route,
                   distance total_distance
              FROM graph
             WHERE p_from LIKE 'A'
             UNION ALL
            SELECT M.p_from,
                   n.p_to,
                   M.full_route || '->' || n.p_to full_route,
                   M.total_distance + n.distance total_distance
              FROM multiroutes M JOIN graph n ON M.p_to = n.p_from
             WHERE n.p_to <> ALL (M.full_route))
     SELECT *
       FROM multiroutes
      WHERE p_to LIKE 'E'
   ORDER BY p_from, p_to, total_distance ASC;

I think by using ORACLE syntax this query may be even more simplified, so while trying somehow I managed to get the expected result, but distance column is not correct -

SELECT CONNECT_BY_ROOT(P_FROM) P_FROM
      ,P_TO
      ,CONNECT_BY_ROOT(P_FROM) || SYS_CONNECT_BY_PATH(P_TO, '->') FULL_ROUTE
      ,DISTANCE TOTAL_DISTANCE
FROM graph
WHERE P_TO = 'E'
START WITH P_FROM = 'A'
CONNECT BY PRIOR P_TO = P_FROM
ORDER BY P_FROM, P_TO, TOTAL_DISTANCE ASC;

Output -

P_FROM  P_TO    FULL_ROUTE  TOTAL_DISTANCE
A       E       A->B->E     22
A       E       A->C->E     29
A       E       A->B->C->E  29

I tried with the query given in this similar answer, but this also doesn't help me much. Is there any approach to get the correct total_distance using ORACLE specific syntax only.

Here is the fiddle for your reference.

Answer 1

You nearly done - your problem is to output only the distance of the last step.

Simple concatenate the expression or the total_distance using + and than evaluate it using xmlquery as proposed here

with dist as (  
SELECT CONNECT_BY_ROOT(P_FROM) P_FROM
      ,P_TO
      ,CONNECT_BY_ROOT(P_FROM) || SYS_CONNECT_BY_PATH(P_TO, '->') FULL_ROUTE
      ,DISTANCE TOTAL_DISTANCE,
      SYS_CONNECT_BY_PATH(DISTANCE,'+')   total_distance_expr
FROM graph
WHERE P_TO = 'E'
START WITH P_FROM = 'A'
CONNECT BY PRIOR P_TO = P_FROM)
select P_FROM, P_TO, FULL_ROUTE, TOTAL_DISTANCE_EXPR
,xmlquery(TOTAL_DISTANCE_EXPR returning content).getNumberVal() as TOTAL_DISTANCE 
from dist
ORDER BY P_FROM, P_TO, TOTAL_DISTANCE ASC;  

This gives the expected result, though I'm wondering if there is even simpler solution...

P_FROM, P_TO, FULL_ROUTE, TOTAL_DISTANCE_EXPR, TOTAL_DISTANCE
A         E     A->B->E     +4+22              26
A         E     A->C->E     +7+29              36
A         E     A->B->C->E  +4+10+29           43