CODEWITHSUNDEEP
mysqlmysql-workbench
andreas
andreas

Reputation: 157

Syntax error when creating procedure in mysql

I have this query.

SELECT distinct c.reportingDate, c.clientId
FROM clients AS c
WHERE NOT EXISTS 
      ( SELECT 1
        FROM accounts_europe AS e
        WHERE e.reportingDate = c.reportingDate
          AND e.clientId = c.clientId

        union

        SELECT 1 -- line A. unexpected select error
        FROM accounts_usa AS u
        WHERE u.reportingDate = c.reportingDate
          AND u.clientId = c.clientId
      ) ;

when executing it at mysql workbench it displays a syntax error which says "unexpected select" at line A.

However, since the query is right, when I execute it, it indeed executes and returns the correct results.

When I try to create a procedure that contains the exact same thing, it cannot be compiled. The source code is this:

CREATE PROCEDURE `proc_sample`()
BEGIN
    SELECT distinct c.reportingDate, c.clientId
    FROM clients AS c
    WHERE NOT EXISTS 
          ( SELECT 1
            FROM accounts_europe AS e
            WHERE e.reportingDate = c.reportingDate
              AND e.clientId = c.clientId

            union

            SELECT 1 -- line A. unexpected select error
            FROM accounts_usa AS u
            WHERE u.reportingDate = c.reportingDate
              AND u.clientId = c.clientId
          ) ;
END

The message displayed is "The object's DDL statement contains syntax errors." Is it something I am doing wrong about this or is it a bug of the mysql workbench?

Upvotes: 1

Views: 2602

Answers (2)

Mike Lischke
Mike Lischke

Reputation: 53357

This error comes up because of a MySQL grammar bug* that has been fixed meanwhile. Try the latest Workbench version instead (currently WB 8.0.11 RC, see "Development Releases" on the download page). That should work for you.

(*) MySQL Workbench uses ANTLR4 for parsing SQL code. That requires a MySQL grammar to generate the parser.

Upvotes: 3

Suresh Prajapati
Suresh Prajapati

Reputation: 4467

Your query is correct syntactically. It's maybe a workbench's bug. Coming to Stored Procedure, you should be able to create it with that query. Try creating SP using below query. In my case, I am able to create SP using this. Copy this query open a new query editor in the workbench and paste it there, select all and press Ctrl + Shift + Enter

USE `YOUR_DB`;
DROP procedure IF EXISTS `proc_sample`;

DELIMITER $$
USE `YOUR_DB`$$
CREATE DEFINER=`admin`@`%` PROCEDURE `proc_sample`()
BEGIN
    SELECT distinct c.reportingDate, c.clientId
    FROM clients AS c
    WHERE NOT EXISTS 
          ( SELECT 1
            FROM accounts_europe AS e
            WHERE e.reportingDate = c.reportingDate
              AND e.clientId = c.clientId

            union

            SELECT 1
            FROM accounts_usa AS u
            WHERE u.reportingDate = c.reportingDate
              AND u.clientId = c.clientId
          ) ;
END$$

DELIMITER ;

Upvotes: 0

Related Questions