title: Not getting full series multiplication (product) in CONNECT-BY query
body: <p>I have a CONNECT BY query that I am not getting desired results from.  </p>
  
  <p>The final sub-query and query are supposed to produce the product of multiplying a string of percentages together.</p>
  
  <p>It starts with a table of retained percentages (PCT) from year to year.  As the query progresses, it's supposed to take the PCT from the first year and multiply it by the PCT from the next year and so on until it reaches the desired depth.  The effect is supposed to be like multiplying down the diagonal in a cross-tab, where the YR (year) and yset represent the start row and column. </p>
  
  <p>Instead of getting the full string as the query progresses, I am only getting the result of the last two percentages.</p>
  
  <p>It may be a simple mistake, but extra eyes will help find it out more quickly.</p>
  
  <p>Code:</p>
  
  <pre><code>     with recurreten as
  (
  select  YR, YSet, 
        rtnpct rtn_year, 
        level lvl, ' - ' s1,   
        rtnpct * nvl( prior rtnpct, 1)  rtnpct 
        --- Below here only for checking the paths
        , sys_connect_by_path( nvl(rtnpct, 1) , '/')  prodpath 
  from Z_RETENTIONPCT
      connect by yr = prior yr+1 and yset  = prior yset+1
      start with YR = 1998  -- :StartYr
                 and
                 yset = 20  -- :StartYSet
  )
  
  -- final results
  select yr, yset, 
        round(rtn_year * 100, 2 ) rtn_year,   
        lvl, -- years the Cumulative Continuation Rate is extended
        s1,  
        round(rtnpct, 2) CCR  
        --- Below here only for checking results
        , rtnpct CCRFull -- Extra digits, for math check
        , prodpath  -- Only used by us, to check the #'s feeding the CCR
  from recurreten
  where  lvl &lt;= 10 -- :Depth     
  order by yr, yset, lvl
  ;
  </code></pre>
  
  <p>I have set up an example in SQLFiddle at <a href="http://sqlfiddle.com/#!4/ce945/1/0" rel="nofollow">http://sqlfiddle.com/#!4/ce945/1/0</a><br>
  The example uses WITH to set up some dummy data.</p>
  
  <p>Results examples would be:
  (desired results)</p>
  
  <pre><code>Year    Col     Reten_yr    Full prod    Full Prod Path
  1998    20      0.84766     0.847660000  = 0.84766
  1999    21      0.77941     0.660674681  = 0.84766 * 0.77941
  2000    22      0.78659     0.519680097  = 0.84766 * 0.77941 * 0.78659
  2001    23      0.76879     0.399524862  = 0.84766 * 0.77941 * 0.78659 * 0.76879
  </code></pre>
  
  <p>(current/wrong results)</p>
  
  <pre><code>Year    Col     Reten_yr    wrong prod   Partial Path
  1998    20      0.84766     0.847660000  = 0.84766
  1999    21      0.77941     0.660674681  = 0.84766 * 0.77941
  2000    22      0.78659     0.613076112  =           0.77941 * 0.78659
  2001    23      0.76879     0.604722526  =                     0.78659 * 0.76879
  </code></pre>
  
  <p>Why am I not getting the full (multiplication) product?  What can I do to fix this?  Anyone...?  Anyone?  Bueller?</p>
  
  <p>UPDATE:  Eat A Peach provided the fix necessary to get truly cumulative results.  I had to update that example due to my hand-tailoring the sample data which hid the scope of the regular data:  rows for 50+ years sequentially, with up to 70 YCS for each year.  The updated query did sequential cumulative products and my requirement is "diagonally sequential cumulative products".  I kept the log-addition solution and added the CONNECT BY back.</p>
  
  <p><a href="http://sqlfiddle.com/#!4/1c326/2" rel="nofollow">http://sqlfiddle.com/#!4/1c326/2</a></p>
  
  <p>Shows some default values for starting points and depth.</p>
  
  <p>Thanks again!</p>