Dealing with recursion (oracle)

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

My client has a table (simplified) like this one:

|| 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 app must show the largest trade chain, a trade chain is defined as the relation within offered and demanded products.

This are trade chains:

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

I try to use C# to solve this, but given the volume of data it became impossible. A coworker of mine told recommends me to use recursion. I try to understand what he means, but I'm a client side developer, with no much knowledge of SQL.

I did these:

  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;

I'm trying to start the recursion with the oldest product on the system (with the min identifiers). But It doesn't work. It gave me all the trade chains. I need just the largest one.

Heres an output example:

OFFERED_PRODUCT_ID      DEMANDED_PRODUCT_ID

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

I believe you want something like

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

The length of the trade chain is equal to the depth of the recursion. Oracle has a psuedo variable, LEVEL, that you can use. If you take your original query and sort by LEVEL desending the end of the longest chain will be first. Then we need to eliminate the rest of the query.

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;

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

You may want to remove the leading comma.

If you want the longest chain, one row at a time:

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

The CONNECT_BY_ROOT command gets data from the root of the recursion, i.e., the first row of the chain. I'm running on 10g Rel 2. I'm not sure if it's available on older versions.

The start with clause you have does almost nothing because the "o." correlates it to the main query. It is equivalent to start with o.OFFERED_PRODUCT_ID is not null. I think you want:

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

The above will give you all chains but won't start any chain "in the middle." If you only want the longest chain you clearly don't want the shorter chains that start "in the middle."