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

Filed under:
|

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

Related posts about oracle11g

Related posts about Oracle