As an example, this one returns EvaluationError of 0:
FROM client_FileB LEFT OUTER JOIN syncHistory ON client_FileB.clientID = syncHistory.keyValue
WHERE syncHistory.keyValue IS NULL
This parses normally, except when $table = "solutionSetting". (Which would make it read like this:)
SELECT DISTINCT syncHistory.keyValue
FROM syncHistory JOIN solutionSetting_FileB ON syncHistory.keyValue = solutionSetting_FileB.solutionSettingID
WHERE syncHistory.syncConnectorFileName = syncConnectorInspectionAideProd
I have a similar query that also works flawlessly for every table name except "solutionSetting". That one looks like this:
What I see here: you don't specify the table in "= syncConnectorInspectionAideProd" is this located in solutionSetting_FileB?
Did you try to quote the field, table-names?
If you wrap the _result like this in data viewer: if ( _result = "?" ; "" ; _result ) and click on "Monitor", you get a more specific error description then with EvaluationError().
The "syncConnectorInspectionAideProd" is a string - a value - not a field. It's a parameter of the first query. Besides, the second one doesn't work either, and it doesn't have a parameter.
The sqlDebug function - which is the calculation you posted - returns a null result. And again, it works flawlessly on the desktop. (EvaluationError returns 0 there.)
I will look at enclosing the table & field names in quotes.
8309 turns out to be "Semantic error" (thanks beverly). Unfortunately, that doesn't give me much of a clue.
if it's a string, you need to put it in single quotes: 'syncConnectorInspectionAideProd' or you put it as parameter with the ?-placeholder...
It IS a parameter. Please read the original post.
Reporting back: Enclosing the table & field names in quotes has no effect. Still error 8309.
Have you tried specifying the JOIN type - INNER JOIN rather than just JOIN? I've had issues with that before.
just another silly question: the TO "solutionSetting" exists?
sorry for loosing scope of your calculated query;-) You have the same error in data viewer, when you write the calculated query? I'm not aware, that "solutionSetting" is kind of a reserved word, so this is odd.
Originally, I had it as “settings”, and though it might be a reserved word. Changed it to “solutionSetting” to try that. No help.
Yes, the proper TOs exist. (In fact, it’s “solutionSetting_FileA” and “solutionSetting_FileB” - corresponding to the two external files being addressed.) I have even deleted the TO and recreated it from scratch. Didn’t change anything.
I’ve checked permissions in the external files; they’re fine.
Really, really puzzling.
Error 8309 relates to incorrect data types being passed: text instead of number for example. Try explicitly casting the data types.
Not according to this page:
Additionally, the data types are identical between the tables that are working and the one that isn’t.
if you'd just get the MBS Plugin (no license required to test), and run the query there through FM.ExecuteSQL function, you'd get text error message.
What does the data viewer "trick" give back? This strings often give more accurate insight
That's a pretty vague error description!
I'd be looking at 'syncConnectorInspectionAideProd' and making sure it's quoted and the correct data type. I know you're way, way more experienced than I could ever hope to be but I've spent many hours battling against FM's implementation of SQL - it can be very picky to say the least - and I learnt a heck of a lot...