KB45766: "You have an error in your SQL syntax" error message appears when running a report with attribute element filter in MicroStrategy Developer 9.3.1 against MySQL Warehouse

Salesforce Solutions Architect • Strategy

SYMPTOM:
"You have an error in your SQL syntax" error message appears when running a report with attribute element filter in Strategy Developer 9.3.1 against MySQL Warehouse as shown below:


QueryEngine encountered error: Execute Query failed. Error type: Odbc error. Odbc operation attempted: SQLExecDirect. [42000:1064: on SQLHANDLE][Strategy][ODBC MySQL Wire Protocol driver][MySQL]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 '6:30:00 PM, 8/7/2010 8:43:20 AM)' at line 3 Connection string:

CAUSE:
Some single quotes around the date values are missing in the SQL statement as shown below:


select from_unixtime(a11.Test_time/1000) Test_Time1
from `jimmy1` a11
where from_unixtime(a11.Test_time/1000) in (10/7/2013 6:30:00 PM, 8/7/2010 8:43:20 AM)

One possible cause for this issue is that the format of attribute form is set as "Datetime" while the data type of column alias is integer as shown below:

ACTION:
Change the data type of the column alias as "Varchar" or proper text format to get correct SQL as shown below:


select from_unixtime(a11.Test_time/1000) Test_Time2
from `jimmy1` a11
where from_unixtime(a11.Test_time/1000) in ('10/7/2013 6:30:00 PM', '8/7/2010 8:43:20 AM')

Comment

0 comments

Details

Knowledge Article

Published:

April 12, 2017

Last Updated:

April 12, 2017