SQLSyntaxErrorException

Technical queries
#1

Hi,

I am getting an SQLSyntaxErrorException when trying to create a report table. I am joining two tables and trying to display some simple data from those two tables, but I just get this error, which I assume is due to the SQL Query which is being built in the background.

The query it generates is:

select “DB_NAME”.“allocation_ticketallocation”.“status” as “status”, “DB_NAME”.“event_session”.“starts” as “starts”, sum(“DB_NAME”.“allocation_ticketallocation”.“id”) as “sum_id” from “DB_NAME”.“allocation_ticketallocation” right outer join “DB_NAME”.“event_session” on (“ZIPCITY_PRODUCTION”.“event_session”.“id” = “DB_NAME”.“allocation_ticketallocation”.“session_id”) group by “DB_NAME”.“allocation_ticketallocation”.“status”, “DB_NAME”.“event_session”.”starts"

And the Error shown is:
Error:SQLSyntaxErrorException: 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 ‘.“allocation_ticketallocation”.“status” as “status”,“DB_NAME”."eve’ at line 2

I can run this query fine in my normal SQL tool if I remove all the quotation marks, so I’d like to understand why it’s adding the quotation marks and if there is a setting or something I should change to prevent it?

#2

Hello
On line 2 we can see that one of the column names are reserved keyword i.e. “id” at “DB_NAME”.“allocation_ticketallocation”.“id. id is a reserved keyword like other keywords like year, month, count, sum etc. Hence because of that is it not working.

You can use any other column. Or change the field name at the db level, then delete the cache at home page. And open the metadta in edit mode and add that table once again in merge mode.

image

Once done then you can try using.