CODEWITHSUNDEEP
mysql
DougLove
DougLove

Reputation: 67

MySQL Cte Statement

I am attempting to run a CTE statement in MySQL, and I am getting an error on the line that holds the WITH. This is my full syntax, what do I need to change so this query executes like I wish.

And this is the error that I receive:

Error Code: 1064. 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 'VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number) ), cte_tally10' at line 7 

SET @v_start_date = '20190101';
SET @v_number_of_months = 12;

WITH
cte_input_values AS (
    SELECT CAST(TIMESTAMPADD(MONTH, TIMESTAMPDIFF(MONTH, 0, @v_start_date), 0) AS date) AS start_date
),
cte_tally10 AS (
    SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cte_tally100 AS (
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 AS month_number
    FROM cte_tally10 c1
    CROSS JOIN cte_tally10 c2
),
cte_months_to_pull AS (
    SELECT TIMESTAMPADD(MONTH, t.month_number, start_date) AS month_to_pull
    FROM cte_tally100 t
    CROSS JOIN cte_input_values
    WHERE t.month_number BETWEEN 0 AND @v_number_of_months - 1
    UNION ALL
    SELECT TIMESTAMPADD(MONTH, t.month_number, TIMESTAMPADD(YEAR, -1, start_date)) AS month_to_pull
    FROM cte_tally100 t
    CROSS JOIN cte_input_values
    WHERE t.month_number BETWEEN 0 AND @v_number_of_months - 1
)
SELECT 
    INSERT(DATE_FORMAT (month_to_pull, 7), 4, 4, '') AS month_year,
    IFNULL(YRS.SaleAmount, 0) AS total_sales
FROM cte_months_to_pull
LEFT OUTER JOIN (
    SELECT TIMESTAMPADD(MONTH, TIMESTAMPDIFF(MONTH, 0, SaleDate), 0) AS SaleMonth,
        SUM(SaleAmount) AS SaleAmount
    FROM CurrentYear
    CROSS JOIN cte_input_values
    WHERE SaleDate >= start_date AND 
        SaleDate < TIMESTAMPADD(MONTH, @v_number_of_months, start_date)
    GROUP BY TIMESTAMPADD(MONTH, TIMESTAMPDIFF(MONTH, 0, SaleDate), 0)
    UNION ALL
    SELECT TIMESTAMPADD(MONTH, TIMESTAMPDIFF(MONTH, 0, SaleDate), 0) AS SaleMonth,
        SUM(SaleAmount) AS SaleAmount
    FROM PrevYear
    CROSS JOIN cte_input_values
    WHERE SaleDate >= TIMESTAMPADD(YEAR, -1, start_date) AND
        SaleDate < TIMESTAMPADD(MONTH, @v_number_of_months, TIMESTAMPADD(YEAR, -1, start_date))
    GROUP BY TIMESTAMPADD(MONTH, TIMESTAMPDIFF(MONTH, 0, SaleDate), 0)
) AS YRS ON SaleMonth = month_to_pull
ORDER BY MONTH(month_to_pull), YEAR(month_to_pull)

Upvotes: 2

Views: 1951

Answers (2)

Paul Spiegel
Paul Spiegel

Reputation: 31812

MySQL doesn't support inline table construction with VALUES. It's though supported by MariaDB

However - As I see, you only need it for cte_tally100 to generate sequence numbers from 0 to 99. But there is a better way to achieve that. See the first example for Recursive Common Table Expressions. The query would be:

with recursive cte_tally100 as (
  select 0 as month_number
  union all
  select month_number + 1
  from cte_tally100
  where month_number < 99
)
...

db-fiddle

If you only need numbers from 0 to 11 - change the WHERE clause to 

where month_number < 11

Update

To get 12 next and 12 previous months, you can use:

SET @start_date = '20190102';
SET @number_of_months = 12;

WITH RECURSIVE
cte_input_values AS (
    # insure @start_date is the first day of the month
    SELECT DATE_FORMAT(@start_date, '%Y-%m-01') AS start_date
),
cte_tally12 AS (
    SELECT 0 as month_number
    UNION ALL
    SELECT month_number + 1
    FROM cte_tally12
    WHERE month_number < @number_of_months - 1
),
cte_months_to_pull AS (
    SELECT start_date + INTERVAL t.month_number MONTH AS month_to_pull
    FROM cte_tally12 t
    CROSS JOIN cte_input_values
    WHERE t.month_number BETWEEN 0 AND @number_of_months - 1
    UNION ALL
    SELECT start_date - INTERVAL 1 YEAR + INTERVAL t.month_number MONTH AS month_to_pull
    FROM cte_tally12 t
    CROSS JOIN cte_input_values
    WHERE t.month_number BETWEEN 0 AND @number_of_months - 1
)
SELECT *
FROM cte_months_to_pull

db-fiddle

That can also be done with a single (recursive) CTE:

SET @start_date = '20190102';
SET @number_of_months = 12;

WITH RECURSIVE
cte_months_to_pull AS (
    # insure @start_date is the first day of the month
    SELECT DATE_FORMAT(@start_date, '%Y-%m-01')
         - INTERVAL @number_of_months MONTH AS month_to_pull
    UNION ALL 
    SELECT month_to_pull + INTERVAL 1 MONTH
    FROM cte_months_to_pull
    WHERE month_to_pull < @start_date + INTERVAL @number_of_months - 2 MONTH
)
SELECT *
FROM cte_months_to_pull

db-fidle

Now you can use cte_months_to_pull for your LEFT JOIN.

Upvotes: 0

Uueerdo
Uueerdo

Reputation: 15961

I could be mistaken, but I don't think MySQL supports that form of VALUES; typically you'd do something like

SELECT * FROM (SELECT 0 AS number UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0) AS numbers

Upvotes: 2

Related Questions