Re: Oracle 11g CTE recursive sql question
- From: Mihajlo Tekic <mihajlo.tekic@xxxxxxxxx>
- To: denis.sun@xxxxxxxxx
- Date: Sat, 7 Jan 2012 00:50:16 -0600
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@xxxxxxxxx> 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
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
