Re: add dummy rows

From: Sayan Sergeevich Malakshinov <malakshinovss_at_psbank.ru>
Date: Fri, 24 May 2013 14:12:10 +0400
Message-ID: <OF6278775D.E2C9FDD9-ON44257B75.00377BD3-44257B75.00383C24_at_psbank.ru>

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

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

Received on Fri May 24 2013 - 12:12:10 CEST