MySQL 8.1 Using Multiple Case Statements in an Insert Gives Ambiguous Error 1064 (42000)

by

I am creating a stored procedure in MySQL 8.1 and have been having issues when doing an insert statement with multiple case statements. I couldn’t find any similar issues on here or anywhere else.

The error returned is:
1064 (42000): 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 ” at line 67

So it does not actually say what the issue is. Just where, but I cant find anything in or around those lines.

Here is the full query:

CREATE PROCEDURE SP_Populate_Tables()
BEGIN
    /* Calendar Load */
    -- Variables Declaration
    SET @StartDate="2020-01-01";
    SET @EndDate="2030-12-31";
    SET @PayPeriodStartDate="2021-12-26";
    SET @PayPeriodEndDate="2022-01-08";

    -- Populate Calendar Table
    TRUNCATE TABLE Calendar;

    -- Loop to populate Calendar Table
    calendar_loop: WHILE @StartDate <= @EndDate DO
        INSERT INTO Calendar (
            `Date`,
            `Month`,
            `Day`,
            `Year`,
            `Month_Name`,
            `Month_Year`,
            `Month_Year_Sort`,
            `Month_Short`,
            `Month_Short_Year`,
            `Quarter_No`,
            `Quarter_Desc`,
            `Week_Num`,
            `Start_Of_Month`,
            `Start_Of_Week`,
            `End_Of_Week`,
            `Pay_Period`,
            `Pay_Period_Start`,
            `Pay_Period_End`,
            `Pay_Period_Pay_Date`
        )
        VALUES (
            @StartDate,
            EXTRACT(MONTH FROM @StartDate),
            EXTRACT(DAY FROM @StartDate),
            EXTRACT(YEAR FROM @StartDate),
            MONTHNAME(@StartDate),
            CONCAT(MONTHNAME(@StartDate), ' ', EXTRACT(YEAR FROM @StartDate)),
            CONCAT(EXTRACT(YEAR FROM @StartDate), LPAD(EXTRACT(MONTH FROM @StartDate), 2, '0')),
            LEFT(MONTHNAME(@StartDate), 3),
            CONCAT(LEFT(MONTHNAME(@StartDate), 3), ' ', EXTRACT(YEAR FROM @StartDate)),
            QUARTER(@StartDate),
            CONCAT('Q', QUARTER(@StartDate)),
            WEEK(@StartDate),
            LAST_DAY(@StartDate - INTERVAL 1 MONTH) + INTERVAL 1 DAY,
            DATE_SUB(@StartDate, INTERVAL DAYOFWEEK(@StartDate) - 1 DAY),
            DATE_ADD(@StartDate, INTERVAL 7 - DAYOFWEEK(@StartDate) DAY),
            CASE
                WHEN LAST_DAY(@StartDate - INTERVAL 1 MONTH) + INTERVAL 1 DAY < @PayPeriodStartDate THEN NULL
                ELSE (DATEDIFF(@StartDate, @PayPeriodStartDate) DIV 14) + 1
            END,
            CASE 
                WHEN LAST_DAY(@StartDate - INTERVAL 1 MONTH) + INTERVAL 1 DAY < @PayPeriodStartDate THEN NULL
                ELSE DATE_ADD(@PayPeriodStartDate, INTERVAL ((DATEDIFF(@StartDate, @PayPeriodStartDate) DIV 14)) * 14 DAY)
            END,
            CASE 
                WHEN LAST_DAY(@StartDate - INTERVAL 1 MONTH) + INTERVAL 1 DAY < @PayPeriodStartDate THEN NULL
                ELSE DATE_ADD(@PayPeriodEndDate, INTERVAL ((DATEDIFF(@StartDate, @PayPeriodStartDate) DIV 14)) * 14 DAY)
            END,
            CASE 
                WHEN LAST_DAY(@StartDate - INTERVAL 1 MONTH) + INTERVAL 1 DAY < @PayPeriodStartDate THEN NULL
                ELSE DATE_ADD(DATE_ADD(@PayPeriodEndDate, INTERVAL ((DATEDIFF(@StartDate, @PayPeriodStartDate) DIV 14)) * 14 DAY), INTERVAL 6 DAY)
            END
        );
        
        SET @StartDate = @StartDate + INTERVAL 1 DAY;
    END WHILE calendar_loop;


END

If I run it using only one of the Case Statements, it works just fine. The below work:

Only Pay_Period

CREATE PROCEDURE SP_Populate_Tables()
BEGIN
    /* Calendar Load */
    -- Variables Declaration
    SET @StartDate="2020-01-01";
    SET @EndDate="2030-12-31";
    SET @PayPeriodStartDate="2021-12-26";
    SET @PayPeriodEndDate="2022-01-08";

    -- Populate Calendar Table
    TRUNCATE TABLE Calendar;

    -- Loop to populate Calendar Table
    calendar_loop: WHILE @StartDate <= @EndDate DO
        INSERT INTO Calendar (
            `Date`,
            `Month`,
            `Day`,
            `Year`,
            `Month_Name`,
            `Month_Year`,
            `Month_Year_Sort`,
            `Month_Short`,
            `Month_Short_Year`,
            `Quarter_No`,
            `Quarter_Desc`,
            `Week_Num`,
            `Start_Of_Month`,
            `Start_Of_Week`,
            `End_Of_Week`,
            `Pay_Period`
            -- `Pay_Period_Start`,
            -- `Pay_Period_End`,
            -- `Pay_Period_Pay_Date`
        )
        VALUES (
            @StartDate,
            EXTRACT(MONTH FROM @StartDate),
            EXTRACT(DAY FROM @StartDate),
            EXTRACT(YEAR FROM @StartDate),
            MONTHNAME(@StartDate),
            CONCAT(MONTHNAME(@StartDate), ' ', EXTRACT(YEAR FROM @StartDate)),
            CONCAT(EXTRACT(YEAR FROM @StartDate), LPAD(EXTRACT(MONTH FROM @StartDate), 2, '0')),
            LEFT(MONTHNAME(@StartDate), 3),
            CONCAT(LEFT(MONTHNAME(@StartDate), 3), ' ', EXTRACT(YEAR FROM @StartDate)),
            QUARTER(@StartDate),
            CONCAT('Q', QUARTER(@StartDate)),
            WEEK(@StartDate),
            LAST_DAY(@StartDate - INTERVAL 1 MONTH) + INTERVAL 1 DAY,
            DATE_SUB(@StartDate, INTERVAL DAYOFWEEK(@StartDate) - 1 DAY),
            DATE_ADD(@StartDate, INTERVAL 7 - DAYOFWEEK(@StartDate) DAY),
            CASE
                WHEN LAST_DAY(@StartDate - INTERVAL 1 MONTH) + INTERVAL 1 DAY < @PayPeriodStartDate THEN NULL
                ELSE (DATEDIFF(@StartDate, @PayPeriodStartDate) DIV 14) + 1
            END
            -- CASE 
            --     WHEN LAST_DAY(@StartDate - INTERVAL 1 MONTH) + INTERVAL 1 DAY < @PayPeriodStartDate THEN NULL
            --     ELSE DATE_ADD(@PayPeriodStartDate, INTERVAL ((DATEDIFF(@StartDate, @PayPeriodStartDate) DIV 14)) * 14 DAY)
            -- END,
            -- CASE 
            --     WHEN LAST_DAY(@StartDate - INTERVAL 1 MONTH) + INTERVAL 1 DAY < @PayPeriodStartDate THEN NULL
            --     ELSE DATE_ADD(@PayPeriodEndDate, INTERVAL ((DATEDIFF(@StartDate, @PayPeriodStartDate) DIV 14)) * 14 DAY)
            -- END,
            -- CASE 
            --     WHEN LAST_DAY(@StartDate - INTERVAL 1 MONTH) + INTERVAL 1 DAY < @PayPeriodStartDate THEN NULL
            --     ELSE DATE_ADD(DATE_ADD(@PayPeriodEndDate, INTERVAL ((DATEDIFF(@StartDate, @PayPeriodStartDate) DIV 14)) * 14 DAY), INTERVAL 6 DAY)
            -- END
        );
        
        SET @StartDate = @StartDate + INTERVAL 1 DAY;
    END WHILE calendar_loop;


END

And this works using the Pay_Period_End column/CASE statement:

calendar_loop: WHILE @StartDate <= @EndDate DO
        INSERT INTO Calendar (
            `Date`,
            `Month`,
            `Day`,
            `Year`,
            `Month_Name`,
            `Month_Year`,
            `Month_Year_Sort`,
            `Month_Short`,
            `Month_Short_Year`,
            `Quarter_No`,
            `Quarter_Desc`,
            `Week_Num`,
            `Start_Of_Month`,
            `Start_Of_Week`,
            `End_Of_Week`,
            -- `Pay_Period`,
            -- `Pay_Period_Start`,
            `Pay_Period_End`
            -- `Pay_Period_Pay_Date`
        )
        VALUES (
            @StartDate,
            EXTRACT(MONTH FROM @StartDate),
            EXTRACT(DAY FROM @StartDate),
            EXTRACT(YEAR FROM @StartDate),
            MONTHNAME(@StartDate),
            CONCAT(MONTHNAME(@StartDate), ' ', EXTRACT(YEAR FROM @StartDate)),
            CONCAT(EXTRACT(YEAR FROM @StartDate), LPAD(EXTRACT(MONTH FROM @StartDate), 2, '0')),
            LEFT(MONTHNAME(@StartDate), 3),
            CONCAT(LEFT(MONTHNAME(@StartDate), 3), ' ', EXTRACT(YEAR FROM @StartDate)),
            QUARTER(@StartDate),
            CONCAT('Q', QUARTER(@StartDate)),
            WEEK(@StartDate),
            LAST_DAY(@StartDate - INTERVAL 1 MONTH) + INTERVAL 1 DAY,
            DATE_SUB(@StartDate, INTERVAL DAYOFWEEK(@StartDate) - 1 DAY),
            DATE_ADD(@StartDate, INTERVAL 7 - DAYOFWEEK(@StartDate) DAY),
            -- CASE
            --     WHEN LAST_DAY(@StartDate - INTERVAL 1 MONTH) + INTERVAL 1 DAY < @PayPeriodStartDate THEN NULL
            --     ELSE (DATEDIFF(@StartDate, @PayPeriodStartDate) DIV 14) + 1
            -- END,
            -- CASE 
            --     WHEN LAST_DAY(@StartDate - INTERVAL 1 MONTH) + INTERVAL 1 DAY < @PayPeriodStartDate THEN NULL
            --     ELSE DATE_ADD(@PayPeriodStartDate, INTERVAL ((DATEDIFF(@StartDate, @PayPeriodStartDate) DIV 14)) * 14 DAY)
            -- END,
            CASE 
                WHEN LAST_DAY(@StartDate - INTERVAL 1 MONTH) + INTERVAL 1 DAY < @PayPeriodStartDate THEN NULL
                ELSE DATE_ADD(@PayPeriodEndDate, INTERVAL ((DATEDIFF(@StartDate, @PayPeriodStartDate) DIV 14)) * 14 DAY)
            END
            -- CASE 
            --     WHEN LAST_DAY(@StartDate - INTERVAL 1 MONTH) + INTERVAL 1 DAY < @PayPeriodStartDate THEN NULL
            --     ELSE DATE_ADD(DATE_ADD(@PayPeriodEndDate, INTERVAL ((DATEDIFF(@StartDate, @PayPeriodStartDate) DIV 14)) * 14 DAY), INTERVAL 6 DAY)
            -- END
        );
        
        SET @StartDate = @StartDate + INTERVAL 1 DAY;
    END WHILE calendar_loop;

Does anyone know why this would be occurring? Happy to provide more details.

Thank you!

Tried reformatting, different variable names, using @ or no @ before the variables.

When I add a DELIMITER // line it immediately throws an error.

  • Considering that mysql v8.1 is still under development, is there any particular reason fo using it?

    – 

  • @Shadow In this case no, however I was able to get around the issue. It was because I was using Azure Data Studio which I guess does not cooperate as well with MySQL. I added MySQL WorkBench to the docker-compose file and from that i was able to create the SP. I can also execute it in Azure Data Studio, just cant create

    – 

  • FWIW, I never use GUI tools for MySQL. Every GUI for MySQL I have used is buggy and introduces weird effects like this. I use the mysql command-line client, or preferably SQL scripts executed using the command-line client.

    – 

Solution:

I had to use MySQL workbench or a different SQL editor/DB tool. I was using Azure Data Studio with the MySQL connector which had been working to this point, but I guess there are still some gaps.

Leave a Comment