MySQL - Unexpected Token

[djvdm]

When trying to use a 'CASE' operator in an SQL Select Statement to base a grid on, I receive an 'unexpected token: THEN' error. The message states that the database returns the error, however, if I run the statement in SQLyog or on the command line, the query works just fine. I get a similar error using 'CONCAT()', 'IF()' and other built in MySQL functions.

Here is a test SQL statement I use that causes the error:

"SELECT CASE reservation_recursive_freq WHEN 'daily' THEN 'a day' WHEN 'weekly' THEN 'a week' WHEN 'monthly' THEN 'a month' END AS test_phrase FROM v_reservation_list_upd WHERE reservation_recursive_freq IN ('daily','weekly','monthly');"

Is there a setting in Alpha that needs to be tweaked?

[Jim Coltz]

Re: MySQL - Unexpected Token

I can confirm I see the same behavior using a similar MySQL query in my database. A search of the board also shows a couple of others have had the same question with no solution given. I suggest you submit a bug report with an example.

[Steve Workings]

Re: MySQL - Unexpected Token

I've run into this sort of thing as well. Alpha can't parse and convert everything, but they do a pretty good job.

Instead of fighting it, I regularly just create UDFs to do the job -- something you might consider until the guys at Alpha make this a priority.

[Jim Coltz]

Re: MySQL - Unexpected Token

You can also base the grid on a view that you build in MySQL with the SQL that Alpha cannot parse.

Steve, how do base a grid on a UDF?

[djvdm]

Re: MySQL - Unexpected Token

I was able to bypass this issue by rewriting my query using OR for the various criteria. This issue also happens with CONCAT() and some other functions, but interestingly enough does not complain with GROUP_CONCAT() or FIND_IN_SET(). I am assuming it may have something to do with Native SQL vs. Portable SQL.

I considered using a view, but I needed to pass a value to a parameter in the ON portion of a left join, which btw refers to a view.

[lvasic]

Re: MySQL - Unexpected Token

In MS SQL I use the Case in a View and have no issues.