Multiple Integer Series

MODEL Method

With MODEL queries there is no need to use the join technique described in Join Method. We can generate multiple series by applying a FOR loop to each row in the base table with the aid of a PARTITION BY clause.

set null "(null)"

break on key duplicates skip 1

select
  key, key2, qty, integer_value
from
  t3
model
  PARTITION BY ( KEY )
  dimension by ( 1 as key2 )
  measures     ( qty, cast( null as integer ) as integer_value )
  rules        ( integer_value[ FOR KEY2 FROM 1 TO QTY[1] INCREMENT 1 ] = cv(key2) )
order by
  key, integer_value
;
 
KEY       KEY2        QTY INTEGER_VALUE
--- ---------- ---------- -------------
a            1 (null)     (null)

b            1          0 (null)

c            1          1             1

d            1          2             1
d            2 (null)                 2

e            1          3             1
e            2 (null)                 2
e            3 (null)                 3
 



Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

  • [url=http://www.sqlsnippets.com/en/topic-12523.html]SQL Snippets: Multiple Integer Series - MODEL Method[/url]

To link to this page in HTML documents or Blogger comments cut and paste this code.

  • <a href="http://www.sqlsnippets.com/en/topic-12523.html">SQL Snippets: Multiple Integer Series - MODEL Method</a>

To link to this page in other web sites use the following values.

  • Link Text : SQL Snippets: Multiple Integer Series - MODEL Method
  • URL (href): http://www.sqlsnippets.com/en/topic-12523.html