Oracle (11.2.0.1.0) - Recursive Cte with a Date Expression

ORACLE (11.2.0.1.0) - Recursive CTE with a date expression

If you want to have a sequential from-date to to-date, Use such this select:

SELECT  DATE '2017-11-01' + LEVEL - 1 AS D, LEVEL AS I
FROM DUAL
CONNECT BY LEVEL <= DATE '2017-11-15' - DATE '2017-11-01' + 1;

Oracle - get previous, current and next year from query

Well, yours isn't correct for e.g. leap years on the last day of that year. Look at 2020:

SQL> select
  2    extract(year from to_date('31.12.2020', 'dd.mm.yyyy') - 365) result
  3  from dual;

    RESULT
----------
      2020

SQL>

See? Turns out that "previous" year for date 31.12.2020 is still 2020.

ADD_MONTHS is safer, I guess:

SQL> select
  2    extract (year from add_months(to_date('31.12.2020', 'dd.mm.yyyy'), -12)) result
  3  from dual;

    RESULT
----------
      2019

SQL>

---

SQL> select
  2    extract (year from add_months(trunc(sysdate), -12)) previous,
  3    extract (year from add_months(trunc(sysdate),   0)) this,
  4    extract (year from add_months(trunc(sysdate),  12)) next
  5  from dual;

  PREVIOUS       THIS       NEXT
---------- ---------- ----------
      2020       2021       2022

SQL>

(this, of course, doesn't require add_months, but I kept it to make query look prettier).

---

Or, why not simply

SQL> select this - 1 as previous,
  2         this,
  3         this + 1 as next
  4  from (select extract(year from sysdate) as this from dual);

  PREVIOUS       THIS       NEXT
---------- ---------- ----------
      2020       2021       2022

SQL>

For 3 rows, use a CTE:

SQL> with temp (this) as
  2    (select extract(year from sysdate) from dual)
  3  select this - 1 as year from temp union all
  4  select this             from temp union all
  5  select this + 1         from temp;

      YEAR
----------
      2020
      2021
      2022

SQL>

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

Since you're on the base release this looks like but 11840579. You may be able to work around it by casting the value - it shouldn't be necessary, but that's bugs for you:

with x (id, dateN) as
(
  select 1, cast(to_date('2015-05-01 00:00:00','YYYY-MM-DD HH24:MI:SS') as date) from dual
  union all
  select id+1, dateN+1 from x where id < 10
)
select * from x;

Including extra elements in the conversion is a bit pointless; personally I prefer date literals anyway:

with x (id, dateN) as
(
  select 1, cast(date '2015-05-01' as date) from dual
  union all
  select id+1, dateN+1 from x where id < 10
)
select * from x;

The two values, date '2015-01-01' and cast(date '2015-05-01' as date) are slightly different types, with different internal representations, which seems to be causing the problem:

select dump(date '2015-05-01', 16) as d1, dump(cast(date '2015-05-01' as date), 16) as d2
from dual;

D1                               D2                             
-------------------------------- --------------------------------
Typ=13 Len=8: df,7,5,1,0,0,0,0   Typ=12 Len=7: 78,73,5,1,1,1,1   

However there's a second part to the bug which is that it can return the wrong results. If you can't patch up to avoid the problem, you could use the older hierarchical-query approach:

select level as id, date '2015-05-01' + level - 1 as dateN
from dual
connect by level < 10;

Can't identify reason for column ambiguously defined error

Field duplicated in the statement, might have something to do with it

h.from_interest_type_cd, h.from_interest_type_cd,

Cycle detection with recursive subquery factoring

From documentation on CONNECT_BY_ISCYCLE:

The CONNECT_BY_ISCYCLE pseudocolumn returns 1 if the current row has a child which is also its ancestor

and that on CYCLE:

A row is considered to form a cycle if one of its ancestor rows has the same values for the cycle columns.

In your example, row 2 does have a child which is also its ancestor, but its id has not been returned yet.

In other words, CONNECT_BY_ISCYCLE checks the children (which are yet to be returned), while CYCLE checks the current row (which is already returned).

CONNECT BY is row based, while recursive CTE's are set-based.

Note that Oracle's documentation on CYCLE mentions an "ancestor row". However, generally speaking, there is no concept of an "ancestor row" in a recursive CTE. It's a set based operation which can yield results completely out of the tree. Generally speaking, the anchor part and the recursive part can even use the different tables.

Since recursive CTE's are usually used to build hierarchy trees, Oracle decided to add a cycle check. But due the set-based way the recursive CTE's operate, it's generally impossible to tell will the next step generate a cycle or not, because without a clear definition of the "ancestor row" cycle condition cannot be defined either.

To perform the "next" step, the whole "current" set needs to be available, but to generate each row of the current set (which includes the cycle column) we just need to have the results of the "next" operation.

It's not a problem if the current set always consists of a single row (like in CONNECT BY), but it is a problem if the recursive operation defined on a set as a whole.

Didn't look into Oracle 11 yet, but SQL Server implements recursive CTE's by just hiding a CONNECT BY behind them, which requires placing numerous restrictions (all of which effectively forbid all set-based operations).

PostgreSQL's implementation, on the other hand, is truly set-based: you can do any operation with the anchor part in the recursive part. It does not have any means to detect cycles, though, because cycles are not defined in the first place.

As was mentioned before, MySQL does not implement CTE's at all (it does not implement HASH JOIN's or MERGE JOINs as well, only the nested loops, so don't be surprised much).

Ironically, I received a letter today on this very subject, which I will cover in my blog.

Update:

Recursive CTE's in SQL Server are no more than CONNECT BY in disguise. See this article in my blog for shocking details:

• SQL Server: are the recursive CTE’s really set-based?

---

Related Topics