02 January 2012

Generate multiple rows

For the first post of the year, one of my New Year's resolutions is to write more posts than last year which shouldn't be too hard, I wanted to collect some different ways of generating multiple records. Sometimes I need, for whatever reason, generate multiple records. These are a few different ways of doing so. This is not really "generating multiple rows", but just a simple way of having multiple lines of output. Just take a big table and limit the number of rows by using "where rownum <= ":
SQL> select 'Happy New Year' msg
  2    from all_objects
  3   where rownum <= 10
  4  /

MSG
--------------
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year

10 rows selected.

SQL>
One of my favorite ways is to use the "Connect by level" trick:
SQL> select 'Happy New Year' msg
  2    from dual
  3  connect by level <= 10
  4  /

MSG
--------------
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year

10 rows selected.

SQL>
Another way is to use the MODEL clause (Oracle 10g). I still find the MODEL clause quite hard to read and understand.
SQL> select msg
  2    from dual
  3    model
  4   dimension by (0 d)
  5   measures (cast ('m' as varchar2(30)) msg)
  6   rules iterate (10)
  7     (msg [iteration_number] = 'Happy New Year')
  8  /

MSG
------------------------------
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year

10 rows selected.

SQL>
And lastly the Recursive Subquery Factoring way (Oracle 11g)
SQL> with lots(r)
  2  as
  3  (select 1 r from dual
  4    union all
  5   select r+1 from lots
  6   where r < 10
  7  )
  8  select 'Happy New Year' msg
  9    from lots
 10  /

MSG
--------------
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year

10 rows selected.
I know there are more ways of generating multiple rows as output, this is not meant as a complete list of all possibilities. This post was meant to sent a subliminal message to wish you a Happy New Year!

3 comments:

  1. Regarding the MODEL clause, you an me both. :)

    I decided the other day I really must write an article on it to force myself to understand it properly. I'm very much a dabbler where it is concerned. :)

    Cheers

    Tim...

    ReplyDelete
  2. SELECT CASE ROWNUM
    WHEN 1 THEN 'Happy '
    WHEN 2 THEN 'New '
    WHEN 3 THEN 'Year '
    WHEN 4 THEN 'To '
    WHEN 5 THEN 'You '
    WHEN 6 THEN 'Too!'
    END
    Msg_part
    FROM DUAL
    CONNECT BY LEVEL <= 6
    /

    ReplyDelete
  3. Great article, I really needed this post. Thanks for sharing!

    - Susan
    Function Rooms North Shore MA

    ReplyDelete