How does the recursive WITH query work in oracle? When does it go into a cycle?

Question

I have a scenario where I have to display a row 'n' number of times depending on the value in its quantity column.

Item       Qty
abc        2 
cde        1

Item       Qty
abc        1
abc        1
cde        1

I am looking to convert the first table to the second.

I came across the site that I should be using the recursive WITH query.

My anchor member returns the original table.

SELECT ITEM, QTY
           FROM lines
          WHERE     
                JOB = TO_NUMBER ('1')
                AND ITEM IN
                       (SELECT PART
                          FROM PICK
                         WHERE DELIVERY = '2')

My recursive member is as follows.

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 > 1

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.

So this is what I had in the end.

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 > 1)
  SELECT ITEM, QTY
    FROM CTE
ORDER BY 1, 2 DESC

I get the following error when I try the above "ORA-32044: cycle detected while executing recursive WITH query"

How is it getting into a cycle? What did I miss in its working?

Also, Upon reading from another website If I used a "cycle clause". I was able to stop the cycle.

The clause I used was.

       CYCLE  
       QUANTITY   
       SET                                    
       END TO '1'                               
       DEFAULT '0'

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?

Answer 1

Oracle Setup:

CREATE TABLE lines ( Item, Qty ) AS
SELECT 'abc', 2 FROM DUAL UNION ALL 
SELECT 'cde', 1 FROM DUAL;

CREATE TABLE pick ( part, delivery ) AS
SELECT 'abc', 2 FROM DUAL UNION ALL
SELECT 'cde', 2 FROM DUAL;

Query 1: Using a hierarchical query:

SELECT Item,
       COLUMN_VALUE AS qty
FROM   lines l
       CROSS JOIN
       TABLE(
         CAST(
           MULTISET(
             SELECT 1
             FROM   DUAL
             CONNECT BY LEVEL <= l.Qty
           )
           AS SYS.ODCINUMBERLIST
         )
       ) t
WHERE  item IN ( SELECT part FROM pick WHERE delivery = 2 )

Query 2: Using a recursive sub-query factoring clause:

WITH rsqfc ( item, qty ) AS (
  SELECT item, qty
  FROM   lines l
  WHERE  item IN ( SELECT part FROM pick WHERE delivery = 2 )
UNION ALL
  SELECT item, qty - 1
  FROM   rsqfc
  WHERE  qty > 1
)
SELECT item, 1 AS qty
FROM   rsqfc;

Output:

ITEM | QTY
:--- | --:
abc  |   1
abc  |   1
cde  |   1

db<>fiddle here