Re: add dummy rows

  • From: Sayan Sergeevich Malakshinov <malakshinovss@xxxxxxxxx>
  • To: exriscer@xxxxxxxxx
  • Date: Fri, 24 May 2013 14:12:10 +0400

If I understand correctly, it would be like that:
(also with formatting: http://pastebin.com/6qZ249t0 )

with t as (
   select  1 c1,   100 C2, to_date('24-MAY-13','dd-mon-yy') MYDATE from 
dual union all
   select  2 c1,   200 C2, to_date('24-MAY-13','dd-mon-yy') MYDATE from 
dual union all
   select  2 c1,   201 C2, to_date('24-MAY-13','dd-mon-yy') MYDATE from 
dual union all
   select  3 c1,   300 C2, to_date('24-MAY-13','dd-mon-yy') MYDATE from 
dual union all
   select  4 c1,   400 C2, to_date('24-MAY-13','dd-mon-yy') MYDATE from 
dual union all
   select  6 c1,   600 C2, to_date('24-MAY-13','dd-mon-yy') MYDATE from 
dual union all

   select  1 c1,   150 C2, to_date('25-MAY-13','dd-mon-yy') MYDATE from 
dual union all
   select  2 c1,   250 C2, to_date('25-MAY-13','dd-mon-yy') MYDATE from 
dual union all
   select  3 c1,   350 C2, to_date('25-MAY-13','dd-mon-yy') MYDATE from 
dual union all
   select  4 c1,   450 C2, to_date('25-MAY-13','dd-mon-yy') MYDATE from 
dual union all
   select  5 c1,   550 C2, to_date('25-MAY-13','dd-mon-yy') MYDATE from 
dual union all
   select  7 c1,   750 C2, to_date('25-MAY-13','dd-mon-yy') MYDATE from 
dual 
)
select mydate,c1_aux,c1,c2
from  t
model
     partition by (mydate)
     dimension by (c1 c1_aux,row_number()over(partition by mydate,c1 order 
by c2) rn)
     measures (c1,c2,max(c1)over(partition by mydate) max_c1,0 dummy)
     rules iterate(1e6) until(iteration_number+1>=max_c1[1,1])
     (
       dummy[iteration_number+1,1]=0
     )
order by mydate,c1_aux;


Best regards,
Sayan Malakshinov
http://orasql.org

--
//www.freelists.org/webpage/oracle-l


Other related posts: