Query with cycle (Oracle 10 XE and 11 XE)

Question

There is roadmap (list of connected cities):

drop table aaa; 
create table aaa(a varchar2(10),  b varchar2(10));
insert into aaa values ('Rome','Berlin');
insert into aaa values ('London','Rome');
insert into aaa values ('London','New-York');
insert into aaa values ('New-York','Dallas');

I need to get path: Berlin=>Rome=>New-York=>Dallas

Variant 1:

select sys_connect_by_path(DECODE(a, PRIOR a, b, a),'=>') PATH1
from aaa
start with a = 'Berlin' or b = 'Berlin'
connect by nocycle  Prior a = b or prior b = a

Return: =>Rome=>London

Variant 2:

select sys_connect_by_path(DECODE(a, PRIOR a, b, a),'=>') PATH1
from aaa
start with a = 'Berlin' or b = 'Berlin'
connect by Prior a = b or prior b = a

Return: ERROR ORA-01436 CONNECT BY loop in user data

Any suggestion, how to get expected result with hierarchical query?

If we insert in the below order query giving the expected result. insert into aaa values (1,0); insert into aaa values (2,1); insert into aaa values (3,2); insert into aaa values (4,3); — VKPRO, Apr 24 '13 at 19:30
i think you meant insert into aaa values (3,2); not insert into aaa values (2,3); — Randy, Apr 24 '13 at 21:03

Egor Skriptunoff · Accepted Answer · 2013-04-25T05:42:40.117

1

select 
  sys_connect_by_path(b,'=>') PATH1
from 
(
  select 
    least(a, b) a, 
    greatest(a, b) b
  from aaa
)
start with a = 0 
connect by prior b = a

UPD :

select 
  sys_connect_by_path(b, '=>') PATH1
from 
  (
    select a, b from aaa
    union all
    select b, a from aaa
    union all
    select null, 'Berlin' from dual
  )
start with a is null
connect by nocycle prior b = a

edited Apr 25 '13 at 05:42

answered Apr 24 '13 at 20:26

Egor Skriptunoff

23,359
2
34
64

Thank you, for your suggestion. But in my problem I can't set an order of values in row. I have rewrite question with new input data to describe so. – potapuff Apr 25 '13 at 04:52
1

@potapuff - Answer updated. The point is to convert your undirected graph to directed one prior to feeding it to Oracle hierarchical query. – Egor Skriptunoff Apr 25 '13 at 05:45

Query with cycle (Oracle 10 XE and 11 XE)

1 Answers1