FAQ
Don't understand why the below query does not give me the next 10 days instead get past 10 days. ( also noticed without the cast it does not work at all)

SQL> select * from v$version
2  ;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
SQL> with t(d,n) as
2  (
3  select cast ( sysdate  as date)  , 1
4  from dual
5  union all
6  select t.d  + 1, t.n +1
7  from t
8  where  t.n < 10
9  )
10  select * from t
11  ;
D                            N
------------------- ----------
2012-01-06 20:35:28          1
2012-01-05 20:35:28          2
2012-01-04 20:35:28          3
2012-01-03 20:35:28          4
2012-01-02 20:35:28          5
2012-01-01 20:35:28          6
2011-12-31 20:35:28          7
2011-12-30 20:35:28          8
2011-12-29 20:35:28          9
2011-12-28 20:35:28         10
10 rows selected.


Denis

Search Discussions

  • Mihajlo Tekic at Jan 7, 2012 at 6:51 am
    Looks like you are hitting Bug 11840579 :-)
    It looks weird, but if you reference the date column twice it returns the
    expected data:

    SQL> with t(d,n) as
    2 (
    3 select cast ( sysdate as date) , 1
    4 from dual
    5 union all
    6 select decode(d,null,d,d +1), n+1
    7 from t
    8 where n<10
    9 )
    10 select * from t;


    D N
    --------- ----------
    07-JAN-12 1
    08-JAN-12 2
    09-JAN-12 3
    10-JAN-12 4
    11-JAN-12 5
    12-JAN-12 6
    13-JAN-12 7
    14-JAN-12 8
    15-JAN-12 9
    16-JAN-12 10

    10 rows selected.

    Another discussion on the same problem:
    https://forums.oracle.com/forums/thread.jspa?threadID=1055057

    Cheers,

    Mihajlo
    On Fri, Jan 6, 2012 at 7:40 PM, Denis wrote:

    Don't understand why the below query does not give me the next 10 days
    instead get past 10 days. ( also noticed without the cast it does not work
    at all)

    SQL> select * from v$version
    2 ;
    BANNER

    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit
    Production
    PL/SQL Release 11.2.0.2.0 - Production
    CORE 11.2.0.2.0 Production
    TNS for Linux: Version 11.2.0.2.0 - Production
    NLSRTL Version 11.2.0.2.0 - Production
    SQL> with t(d,n) as
    2 (
    3 select cast ( sysdate as date) , 1
    4 from dual
    5 union all
    6 select t.d + 1, t.n +1
    7 from t
    8 where t.n < 10
    9 )
    10 select * from t
    11 ;
    D N
    ------------------- ----------
    2012-01-06 20:35:28 1
    2012-01-05 20:35:28 2
    2012-01-04 20:35:28 3
    2012-01-03 20:35:28 4
    2012-01-02 20:35:28 5
    2012-01-01 20:35:28 6
    2011-12-31 20:35:28 7
    2011-12-30 20:35:28 8
    2011-12-29 20:35:28 9
    2011-12-28 20:35:28 10
    10 rows selected.


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


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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJan 7, '12 at 1:41a
activeJan 7, '12 at 6:51a
posts2
users2
websiteoracle.com

2 users in discussion

Denis: 1 post Mihajlo Tekic: 1 post

People

Translate

site design / logo © 2021 Grokbase