ORACLE SQL equivalent of generate_series from Postgresql-CodePudding

I am trying to migrate some code from a postgresql db to an Oracle db. There is a portion of the code that uses generate_series() which is exclusive to postgres.

SELECT 
     ITEM
     generate_series(1, ITEM.QTY:: INTEGER ) as TABLE_ID
FROM TABLE

This creates duplicate records for each ITEM based on the ITEM.QTY value. If the ITEM has a qty of 4 then it will return 4 rows for that ITEM each with a different TABLE_ID of 1, 2 ,3 , and 4.

For example, if the original data looks like this:

ITEM	ITEM.QTY
item1	4

The select statement with generate_series will return:

ITEM	ITEM.QTY
item1	1
item1	2
item1	3
item1	4

What would be the ORACLE SQL equivalent way of doing this?

CodePudding user response：

You may try the following:

SELECT T.ITEM, C.RN itemQty 
FROM tbl T JOIN
(
 SELECT  ROWNUM AS RN
 FROM dual
 CONNECT BY 
 ROWNUM <= (Select MAX(itemQty) From tbl)
) C
ON T.ITEMQTY >= C.RN
ORDER BY T.ITEM, C.RN

You may also use a recursive query as the following:

WITH CTE(item,itemQty, qty) AS
(
  SELECT item, itemQty, 1 FROM tbl
  UNION ALL 
  SELECT item,itemQty, qty 1 
  FROM CTE
  WHERE qty 1 <= itemQty
)
SELECT ITEM, QTY FROM CTE
ORDER BY ITEM, QTY

See a demo.