Base Query errantly says there is a syntax error

English
, , ,
#1

I have set up a query in Base to my MySQL 5.7 backend,

SELECT CONCAT(COALESCE(PERSONNEL.FIRSTNAME, ''), ' ', COALESCE(PERSONNEL.LASTNAME, '')) PERSONNEL, authtypes.AUTHNAME AUTHORIZATION, authorizations.DATE DATE
FROM  PERSONNEL JOIN AUTHORIZATIONS ON
	AUTHORIZATIONS.PERSONNEL = PERSONNEL.ID JOIN AUTHTYPES ON
	AUTHTYPES.ID = AUTHORIZATIONS.AUTHTYPE
ORDER BY PERSONNEL.ID

When I try to run the Query, I get

Syntax error in SQL statement

And when I Tools>SQL and run it that way, it returns what I expect. This seems to be a glitch. Anyone who can explain this behavior so I can avoid it in the future?

#2

First try to execute it with direct SQL (Design view off and SQL pressed). If this works: try to read the whole message, not only “Syntax error in SQL statement”. Could be the GUI has a problem with DATE, because it is also used as a field type.

#3

That’s not it. I tried again, removing the DATE field from the query, and I still got the same error.

#4

Have tested it: For working with the GUI the tables must be masked by double quotes.

GUI accepts only lower cased table names without setting in double quotes. And this should be lower cased names also in original MySQL.

#5

I don’t know. I have several queries set up in the “Queries” section of the GUI that have table names that are all caps, and they’re only surrounded by backticks (that are added automatically whenever I save, close, and re-open the queries in SQL mode). Maybe you’re right, except that both double-quotes and backticks are good enough for setting off the capitalized table names.

#6

Weird… I just tested my original SQL again, and it is working as intended (no syntax error, and the desired results are displayed.) Pretty flakey…maybe I should start using views instead of GUI queries. I like my croissants flakey, not my software…

#7

Double quotes or backticks are added dependent on used driver. Both will do the same.

If you don’t use queries for input data you could also (better) use views instead. Views are saved in the database and are available like write protected tables. If set to direct SQL also comments, returns, spaces and so on will be saved. Better for views, which contain much code.

#8

I’m a newbie here but I see the word “AUTHORIZATION” is bold which might indicate that it’s a key word. Try changing it to something else, like “AUTHOR”

#9

Props, @James_Zimmerman. You may be right about that. Thanks for your input. However, MySQL is not complaining about it. When I run it directly, I get the intended results.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.