Re: Oracle 11g CTE recursive sql question

From: Mihajlo Tekic <mihajlo.tekic_at_gmail.com>
Date: Sat, 7 Jan 2012 00:50:16 -0600
Message-ID: <CAGWRspZ8JgyKh7aBy6_U6_W7LmSzGFyzay1MrTG=0qgpMg8c0Q_at_mail.gmail.com>

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 <denis.sun_at_yahoo.com> 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

Received on Sat Jan 07 2012 - 00:50:16 CST