1064 SQLState:Double Quotes around the alias base-table name
SummaryUnable to run a report definition that is selecting records on a MySQL external database.
Error MessagesAn error occurred on executing the query for the report definition - There was a problem getting a list: code: 1064 SQLState: 42000 Message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use
Steps to Reproduce1. Map a MySQL external database to a class.
2. Create a report definition for that class.
3. Run it and observe it fails.
Root CauseA third-party product issue MySQL server cannot run a sql with Double quotes around the base table name alias,
example : Select "pc0".column1 from base table "pc0";
This sql cannot run on a Mysql server.
It runs successfully on the 5 servers that are certified for report definitions (Oracle, Teradata, DB2, MSSql and Postgres).
ResolutionInstead of using a report definition use a Connect SQL external query to the database and use this Connect SQL rule as part of the activity which in turn will be the data page's source.
Published May 31, 2017 - Updated July 17, 2017