Get Ultimate manager oracle

Question

I have data in the below format.

Emp    to_location    from_location  Vehicle
---------------------------------------------
1      A                B             Road
1      B                C             Ship
1      C                D             Air
1      X                D             Bus

Need the output as

Emp    ToL    FromL  Vehicle
--------------------------
1      A       D   Air

I tried using Connect by and Start with but the result is coming up as below.

Emp   FromL    ToL  Path
--------------------------
1      B       C   Air

I need the output as

Emp   FromL    ToL  Path
--------------------------
1      D       A    Air

The query I have built is like below.

with t as
( select 1 emp, 'A' tloc, 'B' floc, 'Road' v from dual union all
  select 1 emp,'B' tloc, 'C' floc, 'Ship' v from dual union all
  select 1 emp,'C' tloc, 'D' floc, 'Air' v from dual union all
  select 1 emp,'X' tloc, 'D' floc, 'Bus' v from dual
)
select emp,
       connect_by_root floc  from_loc,
       tloc to_location,
       v,
       CONNECT_BY_ISLEAF ch
from T
where CONNECT_BY_ISLEAF=1
CONNECT BY nocycle  prior floc= tloc and prior emp=emp
AND PRIOR SYS_GUID() IS NOT NULL
START WITH tloc ='A'

Can anyone correct the minor thing that I am missing to get the correct output? TIA

Answer 1

I think your query is fine - you just to pick up the root element as you go

SQL> with t as
  2  ( select 1 emp, 'A' floc, 'B' tloc, 'Road' v from dual union all
  3    select 1 emp,'B' floc, 'C' tloc, 'Ship' v from dual union all
  4    select 1 emp,'C' floc, 'D' tloc, 'Air' v from dual union all
  5    select 1 emp,'X' floc, 'D' tloc, 'Bus' v from dual
  6  )
  7  select t.*, level
  8  from T
  9  where emp=1
 10  CONNECT BY nocycle  floc= PRIOR tloc and prior emp=emp
 11  START WITH floc ='A';

       EMP F T V         LEVEL
---------- - - ---- ----------
         1 A B Road          1
         1 B C Ship          2
         1 C D Air           3

to then yield

SQL> with t as
  2  ( select 1 emp, 'A' floc, 'B' tloc, 'Road' v from dual union all
  3    select 1 emp,'B' floc, 'C' tloc, 'Ship' v from dual union all
  4    select 1 emp,'C' floc, 'D' tloc, 'Air' v from dual union all
  5    select 1 emp,'X' floc, 'D' tloc, 'Bus' v from dual
  6  )
  7  select emp,
  8         connect_by_root floc  from_loc,
  9         tloc to_location,
 10         ltrim(sys_connect_by_path(v,'-'),'-') path
 11  from T
 12  where emp=1 and CONNECT_BY_ISLEAF=1
 13  CONNECT BY nocycle   floc= PRIOR tloc and prior emp=emp
 14  START WITH floc ='A';

       EMP F T PATH
---------- - - ------------------------------
         1 A D Road-Ship-Air