Row Generator Using MODEL Clause, # Repeats Table-Based? [message #682113] |
Mon, 05 October 2020 16:27 |
Art Metzer
Messages: 2480 Registered: December 2002
|
Senior Member |
|
|
I know about row generators in the sticky; I'm looking for something ever so slightly different.
Given:
CREATE TABLE t (key NUMBER, text VARCHAR2(1), number_of_repeats NUMBER);
INSERT INTO t VALUES (101, 'A', 3);
INSERT INTO t VALUES (102, 'B', 4);
...I'd like a query that uses Oracle's MODEL clause to "multiply" each row NUMBER_OF_REPEATS times, and include a column I that runs from 1 to NUMBER_OF_REPEATS.
Desired output:
KEY T NUMBER_OF_REPEATS I
---------- - ----------------- ----------
101 A 3 1
101 A 3 2
101 A 3 3
102 B 4 1
102 B 4 2
102 B 4 3
102 B 4 4
I'd like to benchmark a solution that uses Oracle's MODEL clause, but I can't seem to get the syntax quite right, given that the number of repeats comes from my source table.
What's the best way using MODEL to meet my requirement? Thanks.
|
|
|
Re: Row Generator Using MODEL Clause, # Repeats Table-Based? [message #682115 is a reply to message #682113] |
Mon, 05 October 2020 19:21 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
No need to use model:
select t.*,
i
from t,
lateral(
select level i
from dual
connect by level <= number_of_repeats
)
/
KEY T NUMBER_OF_REPEATS I
---------- - ----------------- ----------
101 A 3 1
101 A 3 2
101 A 3 3
102 B 4 1
102 B 4 2
102 B 4 3
102 B 4 4
7 rows selected.
SQL>
But if you want model:
select key,
text,
number_of_repeats,
i
from t
model
partition by(key,text,number_of_repeats)
dimension by(1 d)
measures(number_of_repeats n,1 i)
rules (
i[for d from 2 to n[1] increment 1 ] = cv(d)
)
/
KEY T NUMBER_OF_REPEATS I
---------- - ----------------- ----------
101 A 3 1
101 A 3 2
101 A 3 3
102 B 4 1
102 B 4 2
102 B 4 3
102 B 4 4
7 rows selected.
SQL>
SY.
|
|
|
|