Recursive Subquery Factoring Date Bug in 11gR2

Since Oracle 11gR2, Oracle has supported Recursive Subquery Factoring – this is a more flexible way of doing recursive queries than Oracle’s Connect By. Although 11g is quite old now, it’s still being used in earnest.  Whilst writing some code recently, I noticed there appears to be a problem with them regarding dates. I found this in version 11.2.0.2.0. Let’s take a look at what I found…

with t1(a_date) as (
  select date '2018-12-26'
  from   dual
  union all
  select a_date + 1 -- Problem here
  from   t1
  where a_date + 1 < date '2018-12-30'
)
select * from t1

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

Read More…

Advertisements

Inheriting Values in SQL

Imagine a situation where you have hierarchical data with various values and you wanted to inherit values from ancestors if a given level in the hierarchy had null values. So if a level had a null value, it could inherit that missing value from the parent and if the parent didn’t have a value, the grandparent etc. etc. updwards until we reach the root. How would you do it?  Well in this article I’ll explore a few approaches.
Read more…