Hi,
I am having trouble setting up a recursive CTE with MySQL dataflows, I have seen various articles on recursive dataflows on Domo but do not think they will be much help here, so I am turning to SQL. We currently have budgets corresponding to different campaigns over various date ranges. And example of this is below:
Campaign X | Daily Budget | Start Date | End Date
The idea would be to convert it to show like this
Date | Campaign X | Start Date | End Date | Daily Budget
Where date is every day in the Start and End Date range, I currently have this query in an output dataset in the MySQL dataflow:
WITH cte AS (
SELECT `Campaign Name`, `Start date`,`End date`
FROM `Budgets'
UNION ALL
SELECT `Campaign Name`, DATEADD(day, 1, `Start date`), `End date`
FROM cte
WHERE `Start date` <= `End date`
)
SELECT * FROM cte;
I get the following error message "The database reported a syntax error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'cte AS ( SELECT `Campaign Name`, `Start date`,`End date` FROM' at line 1"
Any help would be greatly appreciated! Thanks