Full path between two nodes cyclic structure pl/sql

I have an input data which consists of ID,prev,current and next node (not sorted). I have to find a path between the first and last page for each ID which covers all the nodes traversed. for eg : if my input data is like : first column is ID, second column is prev_node, third column is current node, fourth column is next node. Prev_node will be empty for starting value and next node will be empty for last value


id  prev  current  next     
1    a      b       c  
1    a      e       f  
1    a      b       g  
1    a      b       o    
1    b      c       d  
1    b      g       h  
1    b      o       p  
1    c      d       a  
1    c      b       g  
1    d      a       e  
1    e      f       e  
1    e      f       f  
1    f      e       f  
1    f      f       f  
1    f      f       a  
1    f      a       b  
1    g      h       i  
1    h      i       j   
1    h      j       i  
1    i      j       i  
1    i      i       k  
1    i      k       l  
1    j      i       i  
1    k      l       m  
1    l      m       n  
1    l      n       a  
1    m      n       a  
1    n      a       b  
1    o      p       q  
1    p      q       r  
1    q      r       s  
1    r      s       t  
1    s      t       u  
1    t      u       v  
1    u      v       w  
1    v      w       x  
1    w      x         
1           a       b  

output should be the path of current node like -

ID    current  
1       a  
1       b  
1       c  
1       d  
1       a  
1       e  
1       f  
1       e  
1       f  
1       f  
1       f  
1      a  
1      b  
1      b  
1      g  
1      h  
1      i  
1      j  
1      j  
1      i  
1      i   
1      k  
1      l  
1      m  
1      n  
1      n  
1      a  
1      b  
1      o  
1      p  
1      q  
1      r  
1      s  
1      t  
1      u  
1     v  
1      w  
1      x  

There will be many IDs with similar data here. I have shown only one ID(1). Also here i have used alphabets which will actually be 200-500 character long string. I tried the SQL approach with little modification, it works fine if an ID has 100 or below rows but gives string concatenation error for more rows (even after converting the long strings to number). Can anyone please suggest a robust procedure based approach to same. I tried some but it doesn't work for more than 300 rows for a ID.


  t_n as (
    select prev, curr, next, rownum n from your_table
  t_br as (
    select prev, curr, 
      '<'||listagg(n, '|<')within group(order by n)||'|' br,
      count(0) cnt
    from t_n
    group by prev, curr
  t_mp as (
      '|'||listagg(list)within group(order by null) list
    from (
      select replace(br, '<') list
      from t_br
      where cnt > 1
  t_path(step, curr, next, used) as (
    select 1, curr, next, ''
    from t_n where prev is null
    union all
    select step + 1, t_br.curr, t_n.next, 
      case when instr(list, '|'||n||'|') = 0 then used
      else used||n||'|' end
    from t_mp, t_path
    join t_br 
      on next = t_br.curr and t_path.curr = prev
    join t_n 
      on n = regexp_substr(br,'^(<('||used||'0)\|)*(<(\d+))?',1,1,'',4)
  ) cycle step set is_cycle to 'Y' default 'N'
select step, curr from t_path order by 1;


Need Your Help

Class cannot be converted to ArrayList (Java)

java arrays arraylist bluej

error message - Game cannot be converted to GameCollection

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.