CODEWITHSUNDEEP
mysql
Miguel Stevens
Miguel Stevens

Reputation: 9220

MySQL Recursive CTE table does not exist

I'm learning about recursive functions, Since I need to extract a row for each day in a range of days. This is my current data

+----+------------+------------+
| id |    from    |     to     |
+----+------------+------------+
|  1 | 09-20-2019 | 09-25-2019 |
+----+------------+------------+

The goal is to receive my data as follows

+----+------------+
| id |    date    |
+----+------------+
|  1 | 09-20-2019 |
|  1 | 09-21-2019 |
|  1 | 09-22-2019 |
|  1 | 09-23-2019 |
|  1 | 09-24-2019 |
|  1 | 09-25-2019 |
+----+------------+

I'm following an example seen here: https://stackoverflow.com/a/54538866/1731057 But for some reason my recursive function is looking for the 'cte' table.

Query 1 ERROR: Table 'project.cte' doesn't exist

WITH cte AS (
    SELECT date_from
    FROM event_dates
  UNION ALL
    SELECT DATE_ADD(event_dates.date_from, INTERVAL 1 DAY) 
    FROM cte
    WHERE DATE_ADD(event_dates.date_from, INTERVAL 1 DAY) <= event_dates.date_until
)
select * FROM cte;

Upvotes: 2

Views: 2872

Answers (2)

Tej Kiran
Tej Kiran

Reputation: 1

**with recursive cte as

(select id,`from` as date from date_range
union all
select id+1,date_add(date,interval 1 day)
from cte
where date<(select `to` from date_range)
)
SELECT id, date_format(date, '%d-%m-%Y') AS date
FROM cte
ORDER BY id, date;

**

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522499

The structure of your recursive CTE is off, and the upper half of the union should be a seed base case. Then, the recursive part should add one day to the previous incoming value:

WITH RECURSIVE cte (n, dt) AS (
    SELECT 1, '2019-09-20'
    UNION ALL
    SELECT n + 1, TIMESTAMPADD(DAY, n, '2019-09-20') FROM cte WHERE n <= 5
)

SELECT * FROM cte;

enter image description here

Demo

Of note, we use TIMESTAMPADD() here to get around the problem of the INTERVAL expression, which can't really take a variable.

If you want to use this approach to generate a series of dates which matches the from and to values in your table, then you can try a join:

SELECT
    t1.dt
FROM cte t1
INNER JOIN yourTable t2
    ON t1.dt BETWEEN t2.from_date AND t2.to_date;

When used this way, the recursive CTE is acting as a calendar table.

Upvotes: 3

Related Questions