MySQL procedure syntax error with create table statement

Question

Below code works perfectly without the create table statement. I have spent an hour just looking at this simple piece of code to find an error.

DELIMITER $$

    USE `operations`$$

    DROP PROCEDURE IF EXISTS `rc_pending_data_tat_proc`$$

    CREATE PROCEDURE `rc_pending_data_tat_proc`()
    BEGIN
    (
    CREATE TABLE rc_pending_tat_temp /*works fine when I remove this*/

    SELECT IF(b.retailer IS NULL,a.retailer, b.retailer) AS Retailer,
     (CASE
     WHEN DATEDIFF(criteria_date,transaction_date)<=50
     THEN '<=50'
     WHEN DATEDIFF(criteria_date,transaction_date) <=70
     THEN '<=70'
     WHEN DATEDIFF(criteria_date,transaction_date) <=80
     THEN '<=80'
     WHEN DATEDIFF(criteria_date,transaction_date) <=90
     THEN '<=90'
     ELSE 
     '>90'
     END) AS Pending_since
     , COUNT(*) AS `count`, CURRENT_TIMESTAMP AS `date`
      FROM `rc_pending_data` a
      LEFT JOIN `rc_store_retailer_mapping` b
      ON a.retailer=b.store_name
    GROUP BY 1,2
    );
        END$$

    DELIMITER ;

The error it gives is pretty standard one i.e.

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 'create table rc_pending_tat_temp SELECT IF(b.retailer IS NULL,a.retailer, b.ret' at line 4

Answer 1

It looks to me like SQL doesn't like the fact that your query is trying to create an empty table.

I just googled around to find out if you could create a table without any columns, and it looks like it's not a generally easy thing to do. If you want to create an empty table for some specific reason, that seems to be a different matter. But if you want to fix this issue, add at least one column to your table. Something like this:

CREATE TABLE rc_pending_tat_temp(Id INT);

That should get your query running.

EDIT:

Sounds like you want to create a new table to store the results of your SELECT statement. In order to do that, check out the syntax of a SELECT INTO statement here. That will allow you to create a table from the results of your SELECT statement.

In that case, you should remove this line:

CREATE TABLE rc_pending_tat_temp

And then add:

INTO rc_pending_tat_temp

So your query should end up looking like this:

DELIMITER $$

    USE `operations`$$

    DROP PROCEDURE IF EXISTS `rc_pending_data_tat_proc`$$

    CREATE PROCEDURE `rc_pending_data_tat_proc`()
    BEGIN
    (
        SELECT IF(b.retailer IS NULL,a.retailer, b.retailer) AS Retailer,
         (CASE
         WHEN DATEDIFF(criteria_date,transaction_date)<=50
         THEN '<=50'
         WHEN DATEDIFF(criteria_date,transaction_date) <=70
         THEN '<=70'
         WHEN DATEDIFF(criteria_date,transaction_date) <=80
         THEN '<=80'
         WHEN DATEDIFF(criteria_date,transaction_date) <=90
         THEN '<=90'
         ELSE 
         '>90'
         END) AS Pending_since
     , COUNT(*) AS `count`, CURRENT_TIMESTAMP AS `date`
      INTO rc_pending_tat_temp
      FROM `rc_pending_data` a
      LEFT JOIN `rc_store_retailer_mapping` b
      ON a.retailer=b.store_name
      GROUP BY 1,2
    );
    END$$

DELIMITER ;

Answer 2

Hi I found the issue by good old hit and try method, it turned out those extra brackets () were causing the syntax error. Removed them and code worked like charm!

DELIMITER $$

    USE `operations`$$

    DROP PROCEDURE IF EXISTS `rc_pending_data_tat_proc`$$

    CREATE PROCEDURE `rc_pending_data_tat_proc`()
    BEGIN
    (
    CREATE TABLE rc_pending_tat_temp /*works fine when I remove this*/

    SELECT IF(b.retailer IS NULL,a.retailer, b.retailer) AS Retailer,
     (CASE
     WHEN DATEDIFF(criteria_date,transaction_date)<=50
     THEN '<=50'
     WHEN DATEDIFF(criteria_date,transaction_date) <=70
     THEN '<=70'
     WHEN DATEDIFF(criteria_date,transaction_date) <=80
     THEN '<=80'
     WHEN DATEDIFF(criteria_date,transaction_date) <=90
     THEN '<=90'
     ELSE 
     '>90'
     END) AS Pending_since
     , COUNT(*) AS `count`, CURRENT_TIMESTAMP AS `date`
      FROM `rc_pending_data` a
      LEFT JOIN `rc_store_retailer_mapping` b
      ON a.retailer=b.store_name
    GROUP BY 1,2
    );
        END$$

    DELIMITER ;

Answer 3

Please remove the "(" after BEGIN and ")" after the GROUP BY 1,2 part of your stored procedure. I tried this without the starting and ending parenthesis and it worked for me.