Hello everyone,
I have a table with 'n' rows.
One column is int data type.
I want to use the data in that int column type to achieve the same amount of rows.
To better understand please consider following sample:
create table test_table (
column1 varchar2 (10 char),
quantity int
)
;
insert all
into test_table (column1, quantity) values ('row1', 5)
into test_table (column1, quantity) values ('row2', 10)
into test_table (column1, quantity) values ('row3', 5)
into test_table (column1, quantity) values ('row4', 75)
into test_table (column1, quantity) values ('row5', 100)
into test_table (column1, quantity) values ('row6', 55)
select 1 from dual;
select *
from test_table;
Now if i do a connect by level in parent query like:
SELECT
LEVEL
,1 AS counter
,conditioned.column1
FROM (
SELECT \*
FROM test\_table
WHERE rownum\<2 )conditioned
CONNECT BY LEVEL\<= conditioned.quantity
And filter to retrieve just one row i have the expected result:
But i want the same expected result for the following rows as well, and want the result set to return those 5 rows of first line on original table, plus the 10 rows for second line on original table, plus 5 rows for third line on original table and so on.
But if i do just for 2 rows it result in 222 rows (not what i expected):
SELECT
LEVEL
,1 AS counter
,conditioned.column1
FROM (
SELECT \*
FROM test\_table
WHERE rownum\<=2 )conditioned
CONNECT BY LEVEL\<= conditioned.quantity
If i do not filter the rows i´ll end up with ORA-01652 (because it is escalating the returning rows in a way i do not want)
I only want 250 rows as result set for the sample data i provided.
How to achieve it with SQL?
Thanks and regards,
Carlos
Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production