Recursive SQL giving ORA-01790
- by PenFold
Using Oracle 11g release 2, the following query gives an ORA-01790: expression must have same datatype as corresponding expression:
with intervals(time_interval) AS
(select trunc(systimestamp)
from dual
union all
select (time_interval + numtodsinterval(10, 'Minute'))
from intervals
where time_interval < systimestamp)
select time_interval from intervals;
The error suggests that the datatype of both subqueries of the UNION ALL are returning different datatypes.
Even if I cast to TIMESTAMP in each of the subqueries, then I get the same error.
What am I missing?
