Advertisement
xtender

add dummy rows

May 24th, 2013
206
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. WITH t AS (
  2.    SELECT  1 c1,   100 C2, TO_DATE('24-MAY-13','dd-mon-rr') MYDATE FROM dual UNION ALL
  3.    SELECT  2 c1,   200 C2, TO_DATE('24-MAY-13','dd-mon-yy') MYDATE FROM dual UNION ALL
  4.    SELECT  2 c1,   201 C2, TO_DATE('24-MAY-13','dd-mon-yy') MYDATE FROM dual UNION ALL
  5.    SELECT  3 c1,   300 C2, TO_DATE('24-MAY-13','dd-mon-yy') MYDATE FROM dual UNION ALL
  6.    SELECT  4 c1,   400 C2, TO_DATE('24-MAY-13','dd-mon-yy') MYDATE FROM dual UNION ALL
  7.    SELECT  6 c1,   600 C2, TO_DATE('24-MAY-13','dd-mon-yy') MYDATE FROM dual UNION ALL
  8.  
  9.    SELECT  1 c1,   150 C2, TO_DATE('25-MAY-13','dd-mon-yy') MYDATE FROM dual UNION ALL
  10.    SELECT  2 c1,   250 C2, TO_DATE('25-MAY-13','dd-mon-yy') MYDATE FROM dual UNION ALL
  11.    SELECT  3 c1,   350 C2, TO_DATE('25-MAY-13','dd-mon-yy') MYDATE FROM dual UNION ALL
  12.    SELECT  4 c1,   450 C2, TO_DATE('25-MAY-13','dd-mon-yy') MYDATE FROM dual UNION ALL
  13.    SELECT  5 c1,   550 C2, TO_DATE('25-MAY-13','dd-mon-yy') MYDATE FROM dual UNION ALL
  14.    SELECT  7 c1,   750 C2, TO_DATE('25-MAY-13','dd-mon-yy') MYDATE FROM dual
  15. )
  16. SELECT mydate,c1_aux,c1,c2
  17. FROM  t
  18. model
  19.      PARTITION BY (mydate)
  20.      DIMENSION BY (c1 c1_aux,ROW_NUMBER()over(PARTITION BY mydate,c1 ORDER BY c2) rn)
  21.      measures (c1,c2,MAX(c1)over(PARTITION BY mydate) max_c1,0 dummy)
  22.      rules iterate(1e6) until(ITERATION_NUMBER+1>=max_c1[1,1])
  23.      (
  24.        dummy[ITERATION_NUMBER+1,1]=0
  25.      )
  26. ORDER BY mydate,c1_aux;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement