title: How does the recursive WITH query work in oracle? When does it go into a cycle?
body: <p>I have a scenario where I have to display a row 'n' number of times depending on the value in its quantity column. </p>
  
  <pre><code>Item       Qty
  abc        2 
  cde        1
  
  Item       Qty
  abc        1
  abc        1
  cde        1
  </code></pre>
  
  <p>I am looking to convert the first table to the second.</p>
  
  <p>I came across the site that I should be using the recursive WITH query.</p>
  
  <p>My anchor member returns the original table.</p>
  
  <pre><code>SELECT ITEM, QTY
             FROM lines
            WHERE     
                  JOB = TO_NUMBER ('1')
                  AND ITEM IN
                         (SELECT PART
                            FROM PICK
                           WHERE DELIVERY = '2')
  </code></pre>
  
  <p>My recursive member is as follows.</p>
  
  <pre><code>SELECT CTE.ITEM, (CTE.QTY - 1) QTY
             FROM CTE
                  INNER JOIN
                  (SELECT ITEM, QTY
                     FROM LINES
                    WHERE JOB_ID = TO_NUMBER ('1')
                          AND ITEM IN
                                 (SELECT PART
                                    FROM PICK
                                   WHERE DELIVERY = '2'
                                         )) T
                     ON CTE.ITEM = T.ITEM
            WHERE CTE.QTY &gt; 1
  </code></pre>
  
  <p>My goal is to get all the parts and quantities first then and then for all parts with qty > 1 in the recursive step generate new rows to be added to the original result set and qty displayed in the new rows would be (original qty for that part - 1). The recursion would go on until qty becomes 1 for all the parts. </p>
  
  <p>So this is what I had in the end.</p>
  
  <pre><code>WITH CTE (ITEM, QTY)
  AS (
  SELECT ITEM, QTY
             FROM lines
            WHERE     
                  JOB = TO_NUMBER ('1')
                  AND ITEM IN
                         (SELECT PART
                            FROM PICK
                           WHERE DELIVERY = '2')
  UNION ALL
  SELECT CTE.ITEM, (CTE.QTY - 1) QTY
             FROM CTE
                  INNER JOIN
                  (SELECT ITEM, QTY
                     FROM LINES
                    WHERE JOB_ID = TO_NUMBER ('1')
                          AND ITEM IN
                                 (SELECT PART
                                    FROM PICK
                                   WHERE DELIVERY = '2'
                                         )) T
                     ON CTE.ITEM = T.ITEM
            WHERE CTE.QTY &gt; 1)
    SELECT ITEM, QTY
      FROM CTE
  ORDER BY 1, 2 DESC
  </code></pre>
  
  <p>I get the following error when I try the above
  "ORA-32044: cycle detected while executing recursive WITH query"</p>
  
  <p>How is it getting into a cycle? What did I miss in its working?</p>
  
  <p>Also, Upon reading from another website If I used a "cycle clause". I was able to stop the cycle.</p>
  
  <p>The clause I used was.</p>
  
  <pre><code>       CYCLE  
         QUANTITY   
         SET                                    
         END TO '1'                               
         DEFAULT '0'
  </code></pre>
  
  <p>If I used this before the select statement. I'm getting the desired output but I don't feel this is the right way of going about it. What exactly is the clause doing? What is the right way of using it? </p>