Recursive SQL giving ORA-01790
Posted
by PenFold
on Stack Overflow
See other posts from Stack Overflow
or by PenFold
Published on 2010-04-06T16:50:48Z
Indexed on
2010/04/06
16:53 UTC
Read the original article
Hit count: 345
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?
© Stack Overflow or respective owner