[Solved] GROUP_CONCAT SEPARATOR problem

[mogua]

Backend - MySQL 5.1
Frontend - OpenOffice.org Base 3.1.0

I have written the following query:

Code: Select all

SELECT
	`passage_keyword`.FK_REFERENCE,
	GROUP_CONCAT(`passage_keyword`.FK_KEYWORD SEPARATOR ' ')
FROM
	`armedanddangerous`.`passage_keyword` AS `passage_keyword`
GROUP BY
	`passage_keyword`.FK_REFERENCE

MySQL Query Browser results:

Code: Select all

FK_REFERENCE          GROUP_CONTACT(`passage_keyword`.FK_KEYWORD SEPARATOR ' ')
example_reference     keyword1 keyword2 keyword3 keyword4

OpenOffice.org Base query results:

Code: Select all

SQL Status: HY000
Error code: 1000

Syntax error in SQL expression

syntax error, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIKE

Btw, the query works fine in OpenOffice if I omit the "SEPARATOR ' '" text. But then, of course, I get the default concatenated results using a ',' character instead of a space (or anything else I want to specifiy).

Any idea what is wrong?

[eremmel]

Please read this reply in the post and note the difference between Base-parsed SQL and Direct-SQL.

[mogua]

Thanks! That explained it just great. I clicked the "Run SQL command directly" in the SQL view, and it processed the query perfectly.