Sum() in Oracle SQL connect by

Question

I am trying to solve oracle SQL scenario. I have to find all possible path between two places and calculate the total distance between them , number of stops between the two places and the path, using SQL query.The Source table contains 3 columns i.e. Departure_city , Arrival_City and Distance .

I wrote a SQL query and founded the number of stops and the path.But I couldn't find the total distance. My Query is:

 select * from(select arrive , level-1 , sys_connect_by_path (depart ,',')   
    from travel  
    start with depart = 'Mexico'  
    connect by nocycle prior arrive=depart)  
    where arrive = 'New York';  

Here I took Delhi as Departure_city and Bangalore as Arrival city .

Answer 1

This is kind of tricky, since you want to get the sum for the path taken by the recursive query. This solution is a little unorthodox, but should work:

CREATE OR REPLACE FUNCTION prod.eval (p_equation VARCHAR2)
   RETURN NUMBER IS
   v_result NUMBER;
BEGIN
   IF LENGTH (TRIM (TRANSLATE (p_equation, '1234567890+-()*/', ' ')))
         IS NOT NULL THEN
      raise_application_error (
         -20000,
         'EVAL: Parameter contains non mathematical values');
   END IF;

   EXECUTE IMMEDIATE ' begin :1 := ' || p_equation || '; end;'
      USING OUT v_result;

   RETURN v_result;
END;
/

select * from(select arrive , 
                     level-1 , 
                     sys_connect_by_path (depart ,',') as hops
                     eval('0' || sys_connect_by_path (distance ,'+')) as distance
              from travel  
              start with depart = 'Delhi'  
              connect by nocycle prior arrive=depart)  
where arrive = 'Bangalore';