Home » SQL & PL/SQL » SQL & PL/SQL » Row Generator Using MODEL Clause, # Repeats Table-Based?
Row Generator Using MODEL Clause, # Repeats Table-Based? [message #682113] Mon, 05 October 2020 16:27 Go to next message
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
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.
Re: Row Generator Using MODEL Clause, # Repeats Table-Based? [message #682139 is a reply to message #682115] Tue, 06 October 2020 09:39 Go to previous message
Art Metzer
Messages: 2480
Registered: December 2002
Senior Member
Solomon, these look great, I will benchmark both.

Thank you!

Art
Previous Topic: Test Oracle SQL
Next Topic: Loop with Dates
Goto Forum:
  


Current Time: Thu Mar 28 08:00:32 CDT 2024