title: Oracle 10g Recursive Query
body: <p>I have the following recursive query which would run in 11g but is not supported in Oracle 10g database:</p>
  
  <pre><code>with st as (
      select
          rownum id,
          case  
              when rownum-1 &lt; 1 then null
              else rownum-1
          end parent_id,
          customer, 
          tickets
      from tickets
  ),
  st2(id, parent_id, customer, tickets, offset) as (
          select
              id, parent_id, shuffler_id, subno, contrno, tickets, 0 offset
          from st
          where id = 1
      union all
          select
              st2.id, st2.parent_id, st2.tickets, (st.tickets + st.offset) offset
          from st, st2
          where st2.parent_id = st.id
  )
  select * from st2
  </code></pre>
  
  <p>What I'm trying to do is to calculate an offset for each row based on its previous row columns <code>tickets</code> and <code>offset</code> (same calculated column), and I'm starting the first row with an offset of 0; the fact that I need to depend on a column that I'm calculating during the execution of the query requires recursion.</p>
  
  <p>The issue is the above query is not supported in Oracle 10g, so I tried to use <code>connect by</code> instead which works, but the ugly part is it's extremely inefficient:</p>
  
  <pre><code>with st as (
      select
          rownum id,
          case  
              when rownum-1 &lt; 1 then null
              else rownum-1
          end parent_id, 
          customer,
          tickets
      from tickets
  )
  select
      id, 
      parent_id,
      customer,
      tickets,
      (
          select nvl(sum(tickets), 0) from st
          where level &lt;  x.id
          start with id = 1
          connect by prior id = parent_id
      ) offset
  from st x
  </code></pre>
  
  <p>Using the 2nd query I'm summing all the previous rows, which does the job but is redundant as well and I cannot depend on when this table grows to millions of records.</p>
  
  <p>Any idea on how to implement something similar to the first query in Oracle 10g database?</p>