Re: add dummy rows

  • From: Sayan Malakshinov <xt.and.r@xxxxxxxxx>
  • To: exriscer@xxxxxxxxx
  • Date: Fri, 24 May 2013 14:41:52 +0400

Example:
http://pastebin.com/KL5VCxsr
WITH t AS (
   SELECT  1 c1,   100 C2, TO_DATE('24-MAY-13','dd-mon-rr') 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
      (SELECT level-1 c1_aux FROM dual CONNECT BY level<24) gen
      left join t
      PARTITION BY (mydate)
      ON c1=c1_aux
ORDER BY mydate,c1_aux;


On Fri, May 24, 2013 at 1:47 PM, Ls Cheng <exriscer@xxxxxxxxx> wrote:

> Hi
> The example I posted was with test data. The real thing is that we have
> data for each hour in a day (24 rows) but when Daylight Savings Time kicks
> in we will have 25 rows because the hour 2 is accounted twice in that
> specific day.
>
> So basically I have to deal with 0 to 24 rows (this is because some hours
> possible has no data) in normal days and 0 to 25 rows in DST days, to write
> a generic query to deal with this data I have thought of generate always 25
> rows no matter day and hour, even those hours with no data.
>
> The data can look like
>
> *DATE        HOUR  VALUE
> ----------- ------ ------
> 24-MAR-2013 0     0
> 24-MAR-2013 1     100
> 24-MAR-2013 2     200
> 24-MAR-2013 3     300
> 24-MAR-2013 4     400
> 24-MAR-2013 5     500
> 24-MAR-2013 7     700
> 24-MAR-2013 9     900
> 24-MAR-2013 13    1300
> 24-MAR-2013 15    1500
> 24-MAR-2013 16    1600
> 24-MAR-2013 17    1700
> 24-MAR-2013 23    2300
> 30-MAR-2013 0     0
> 30-MAR-2013 1     100
> 30-MAR-2013 2     200
> 30-MAR-2013 2     200
> 30-MAR-2013 4     400
> 30-MAR-2013 5     500
> 30-MAR-2013 8     800
> 30-MAR-2013 11    1100
> 30-MAR-2013 14    1400
> 30-MAR-2013 15    1500
> 30-MAR-2013 16    1600
> 30-MAR-2013 20    2000
> 30-MAR-2013 23    2300*
>
> 30 of March was the DST day this year
>
> Thanks
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>


-- 
Best regards,
Sayan Malakshinov
Senior performance tuning engineer
PSBank
http://orasql.org


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


Other related posts: