add ;"";"";"" ) after the 2.16'"
Thanks. Sadly to no avail just yet. No longer getting too few arguments, now it returns just a ?
? Means invalid expression. The arguments were misng, but now double check spellings and capitalizations .
I don't see an error in the syntax of your query. As has already been suggested, double check field and table occurrence names for accuracy.
There are ways to get more info on that ? result out of the query if testing this as a watch expression in the data viewer. The SQL.Debug custom function is one way.
You might also post a screen shot of your calculation. You may have subconsciously corrected the error when you posted the query here and thus the error may exist in your solution and not here in this discussion.
As brian said, you need the fieldSeparator and rowSeparator parameters:
If you pass other optional parameters, they are added after these two required parameters. The 'defaults' (if you use the "") are Comma and Carriage return, respectively.
A testing tip or two:
- add EvalutationError() around the ExecuteSQL() to see if an error code is returned.
- if you have FMPAdvanced 16, the error message can be returned in Data Viewer
- earlier versions (Advanced) will benefit from this article:
2 of 2 people found this helpful
If you really want to be good at SQL, get a tool (that works with FMP) that's also good at SQL. IMHO, even in 16, FMP still doesn't have close to a decent SQL editor.
I'd try something like RazorSQL. Once you set it up using FMP's free JDBC driver, it's amazing. You'll automatically get real error messages, tool-tip assist as you're typing, tons of exports, RegEx searching results (if you need to), etc.. Best yet, Razor works with every database out there so it's not just FMP. You can try it for free. (It's only $99)
Thanks for all the help thus far.
I cleaned it up a bit and using MBS I was able to get an error code.
MBS("FM.ExecuteSQL";" SELECT \"DateFinishedSimple\"
FROM \"ORDERPHASETASK\" INNER JOIN \"RELEASE\"
ON \"_ReleaseID\" = \"__pk_ReleaseID\"
WHERE \"ShannonTaskNumberlu\" = '2.16'";"";"";"")
[MBS] ERROR: FQL0018/(1:135): An expression contains incompatible data types.
Field Types in the query are listed below.
DateFinishedSimple - Date (just made this field in case the timestamp was the issue)
ShannonTaskNumberlu - Number
_ReleaseID - Number
__pk_ReleaseID - Number
I believe there was another recent discussion with SQL and date format. I wasnt following the thread, but maybe something relevant is in there?
Also, for fun try removing the single quotes from '2.16' i think SQL views that as a string then converts it to a number. I have read that decimals can be rounded during that conversion. Not 100% how accurate that is
1 of 1 people found this helpful
Data types are only going to be an issue for fields that are part of an expression--either the join or the WHERE in this query.
Try removing the single quotes from about the 2.16 and see if that eliminates the error.
If you post your solution, or at least some non-sensitive-yet-representative data from it, I'll show you how utterly simple your query is with a real SQL tool.
That was it!! Thanks sir!
I am researching RazorSQL now actually. I will work on a stripped down version to discuss further. Thanks for all the help.
Shoot. Spoke too soon.
Query is working. However I am still getting all ORDERPHASETASK items not just the ones that are related to the given record via the releaseID relationship.....
I'll be glad to help.
And, keep researching Razor, or a tool like it, for .... "the next time".