0

I ran following code in 11203 Oracle database, it worked fine without errors:

WITH xtimes (xdate) AS  
(
SELECT to_date('22-OCT-13 21:12:23','DD-MON-RR HH24:MI:SS') xdate FROM dual  
UNION ALL  
SELECT xdate+(15/1440) FROM xtimes WHERE xdate+(15/1440) <= to_date('23-OCT-13 21:10:27','DD-MON-RR HH24:MI:SS')
)
select xdate from xtimes

However if it's ran in a version lower than 11203, like 11202, it failed:

SELECT xdate+(15/1440) FROM xtimes WHERE xdate+(15/1440) <= to_date('23-OCT-13 21:10:27','DD-MON-RR HH24:MI:SS')
            *

ERROR at line 5: ORA-01790: expression must have same datatype as corresponding expression

How to change the above code so that it can be ran in 11202 db?

juergen d
  • 201,996
  • 37
  • 293
  • 362
Amos
  • 23
  • 5
  • 2
    Recursive CTEs are not availabe in earlier versions. You need to rewrite it using `CONNECT BY` –  Oct 24 '13 at 14:58

1 Answers1

1

Try this query, it works on earlier versions of Oracle - as I remember from version 8 or 9.

select to_char( to_date('22-12-13 21:12:23','DD-Mm-RR HH24:MI:SS') 
                + (level-1) * 15/1440, 'rr-mm-dd hh24:mi' )  xdate
from dual
connect by to_date('22-12-13 21:12:23','DD-mm-RR HH24:MI:SS')
       + (level-1) * 15/1440  
       <= to_date('23-12-13 21:10:27','DD-mm-RR HH24:MI:SS')
krokodilko
  • 35,300
  • 7
  • 55
  • 79