Work with recursion (oracle)

Question

I have problems trying to do some business logic for a client (web trading):

My client has a table (simplified) like this:

|| ID ||  OFFERED_PRODUCT_ID  || DEMANDED_PRODUCT_ID ||
|| 01 ||  34                  || 45                  ||
|| 01 ||  34                  || 16                  ||
|| 01 ||  45                  || 57                  ||
|| 01 ||  47                  || 57                  ||
|| 01 ||  57                  || 63                  ||
|| 01 ||  16                  || 20                  ||

Now the application should show the largest trading chain, the trade chain is defined as the ratio within the proposed and required products.

These are the trade chains:

<34, 45, 57, 63 <34, 16, 20 <45, 57, 63 <47, 57, 63 <57, 63 <16, 20 </p>

I am trying to use C # to solve this problem, but given the amount of data this has become impossible. My colleague said that I recommend using recursion. I'm trying to figure out what he means, but I'm a client-side developer who doesn't know SQL.

I have done this:

  select  o.OFFERED_PRODUCT_ID, o.DEMANDED_PRODUCT_ID
  from TRADES o
  start with 
  o.DEMANDED_PRODUCT_ID = (SELECT MIN(o.DEMANDED_PRODUCT_ID) from TRADES)
  connect by NOCYCLE prior o.DEMANDED_PRODUCT_ID = o.OFFERED_PRODUCT_ID;

( ). . . .

:

OFFERED_PRODUCT_ID      DEMANDED_PRODUCT_ID

9920896475501851        59587794888502550724
59587794888502550724    13197303523502765990
13197303523502765990    54010274740204405159
54010274740204405159    14505831337880766413
14505831337880766413    89607128670993987443
89607128670993987443    8802863939059413452
8802863939059413452  7779127922701247342
7779127922701247342  3810800421539873909
3810800421539873909  12423373218147473557

Answer 1

. Oracle psuedo, LEVEL, . LEVEL, . .

select *
  from
  ( select  o.OFFERED_PRODUCT_ID, o.DEMANDED_PRODUCT_ID
      from TRADES o
      start with    o.DEMANDED_PRODUCT_ID = 
                (SELECT MIN(o.DEMANDED_PRODUCT_ID) from TRADES)
      connect by NOCYCLE prior o.DEMANDED_PRODUCT_ID = o.OFFERED_PRODUCT_ID
      order by level desc
   )
  where rownum = 1;

The above will return the last row in the chain. To get the entire chain in a comma seperated list:

select lvl,   opath ||','|| DEMANDED_PRODUCT_ID chain
from (
select level lvl,  o.OFFERED_PRODUCT_ID, o.DEMANDED_PRODUCT_ID, SYS_CONNECT_BY_PATH(o.OFFERED_PRODUCT_ID, ',') opath
  from TRADES o
  start with    o.DEMANDED_PRODUCT_ID = (SELECT MIN(o.DEMANDED_PRODUCT_ID) from TRADES)connect by NOCYCLE prior o.DEMANDED_PRODUCT_ID = o.OFFERED_PRODUCT_ID
  order by level desc
)
where rownum = 1

.

, :

select c.OFFERED_PRODUCT_ID, c.DEMANDED_PRODUCT_ID
  from TRADES c
  start with  (c.OFFERED_PRODUCT_ID,  c.DEMANDED_PRODUCT_ID) in
            (
             select o1,  d1
              from (
                       select level lvl,  o.OFFERED_PRODUCT_ID, o.DEMANDED_PRODUCT_ID
                          , CONNECT_BY_ROOT OFFERED_PRODUCT_ID   o1
                          , CONNECT_BY_ROOT DEMANDED_PRODUCT_ID   d1
                         from TRADES o
                         start with    o.OFFERED_PRODUCT_ID not in (select st.DEMANDED_PRODUCT_ID from trades st)
                         connect by NOCYCLE prior o.DEMANDED_PRODUCT_ID = o.OFFERED_PRODUCT_ID
                        order by level desc
                    )
                  where rownum = 1
            )
   connect by NOCYCLE prior c.DEMANDED_PRODUCT_ID = c.OFFERED_PRODUCT_ID

CONNECT_BY_ROOT , .. . 10g Rel 2. , .

Answer 2

, -

SQL> ed
Wrote file afiedt.buf

  1  with trades as (
  2    select 34 offered_product_id, 45 demanded_product_id from dual
  3    union all
  4    select 34, 16 from dual
  5    union all
  6    select 45, 57 from dual
  7    union all
  8    select 47, 57 from dual
  9    union all
 10    select 57, 63 from dual
 11    union all
 12    select 16, 20 from dual
 13  )
 14  select path
 15    from (
 16    select offered_product_id,
 17           demanded_product_id,
 18           level + 1,
 19           sys_connect_by_path( offered_product_id, '/' ) ||
 20              '/' || demanded_product_id path,
 21           dense_rank() over (order by level desc) rnk
 22      from trades
 23   connect by prior demanded_product_id = offered_product_id )
 24*  where rnk = 1
SQL> /

PATH
--------------------
/34/45/57/63

Answer 3

start with, , , "o." . start with o.OFFERED_PRODUCT_ID is not null. , :

select level,  o.OFFERED_PRODUCT_ID, o.DEMANDED_PRODUCT_ID, SYS_CONNECT_BY_PATH(o.OFFERED_PRODUCT_ID, ','), SYS_CONNECT_BY_PATH(o.DEMANDED_PRODUCT_ID, ',')
   , CONNECT_BY_ROOT OFFERED_PRODUCT_ID   o1
   , CONNECT_BY_ROOT DEMANDED_PRODUCT_ID   d1
  from TRADES o
  start with    o.OFFERED_PRODUCT_ID not in (select st.DEMANDED_PRODUCT_ID from trades st)
  connect by NOCYCLE prior o.DEMANDED_PRODUCT_ID = o.OFFERED_PRODUCT_ID

, "". , , "".