MySQL Recursive CTE - Date Range Split
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
Best Answer
-
If you SELECT VERSION() you'll see which version of MySQL you're developing against, MySQL 5.6,
https://dev.mysql.com/doc/refman/8.0/en/with.html
MySQL 5.6 does not support CTEs
But if you have a list of Dates (a date dimension) you could accomplish the same thing with a simple JOIN
SELECT
a.*
FROM
a
Join
Date d
on d.date between startDate and endDate
or similar.
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"4
Answers
-
If you SELECT VERSION() you'll see which version of MySQL you're developing against, MySQL 5.6,
https://dev.mysql.com/doc/refman/8.0/en/with.html
MySQL 5.6 does not support CTEs
But if you have a list of Dates (a date dimension) you could accomplish the same thing with a simple JOIN
SELECT
a.*
FROM
a
Join
Date d
on d.date between startDate and endDate
or similar.
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"4 -
Wow thanks a lot, this did what I needed it to do perfectly, and much much simpler than trying to write a recursive CTE in the first place! Thanks a lot for the help
2
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive