Oracle "with .. as (select ...)" not work in database version lower than 11203

0
votes

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?

1
sqloraclewith-statement
Recursive CTEs are not availabe in earlier versions. You need to rewrite it using CONNECT BY - a_horse_with_no_name

1 Answers

1
votes

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')