Oracle 11g CTE recursive sql question

  • From: Denis <denis.sun@xxxxxxxxx>
  • To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 6 Jan 2012 17:40:28 -0800 (PST)

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


Other related posts: