ORACLE SQL equivalent of generate_series from Postgresql

Question

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?

Does this answer your question? [ORACLE SQL:Get all integers between two numbers](https://stackoverflow.com/questions/1453747/oracle-sqlget-all-integers-between-two-numbers). Or for new range per some ID: [Generating dates between two dates](https://stackoverflow.com/questions/16207543/generating-dates-between-two-dates) — astentx, Oct 05 '22 at 08:51
i think the idea behind your link is similar to what @ahmed did — hwestblvd, Oct 05 '22 at 19:39
Of course, but this is a goal: do not answer the same questions again — astentx, Oct 05 '22 at 20:43

ahmed · Accepted Answer · 2022-10-05T02:11:07.143

2

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.

edited Oct 05 '22 at 02:11

answered Oct 05 '22 at 01:09

ahmed

9,071
3
9
22

Is there a way to create the ID based on ITEM so that it restarts at 1 for each new ITEM? Using your demo, I added an item2 with qty 3 and the ITEMQTY number increments to 5 instead of ending at at 4 for item1 and then creating 3 new rows for item2. I am assuming https://dbfiddle.uk/g96wjE3J – hwestblvd Oct 05 '22 at 02:01
Yes, I'll update the answer – ahmed Oct 05 '22 at 02:02
Tried implementing the first solution but the max(itemqty) from my database is 31000 so i stopped it after it ran for 2+ hours. I tried it with a fixed value instead of max(itemqty) so i know it works. When I try the CTE solution, I get ORA-00942 (the table does not exist) , ORA-02063 (preceding line from ) , and ORA-02063 (preceding 2 lines from ). Do you have any suggestions on what else to try? The table I am pulling from has about 250k ITEMS and the max(itemQty) is 31000 so doing that many joins using the first solution doesnt seem feasible. – hwestblvd Oct 06 '22 at 17:32
Roughly speaking, let us say that the average of itemqty is 15k, then the returned rows will be(250K items * 15k) which is too much (3750M). Maybe performing [pagination](https://stackoverflow.com/questions/241622/paging-with-oracle) on the outer query will help. – ahmed Oct 11 '22 at 05:24

ORACLE SQL equivalent of generate_series from Postgresql

1 Answers1