This may or may not help:
Also the (?) may be the problem.
You might do this:
AND targetId IN ?
with the parameter like this
"(" & Substtitute(RES_Res::_fk_links;"¶";",") & ")"
You could even make it a variable that is then called in the ExcuteSQL function
Let ( $myList = "(" & Substtitute(RES_Res::_fk_links;"¶";",") & ")"
ExecuteSQL( s; ":" ; "" ; 4 ; $myList )
WHERE \"_fk_PanelId\" = ? AND targetId IN (?) ORDER BY targetId";
ExecuteSQL ( s ; ";" ; "¶" ; 4; Substitute(RES_Res::_fk_links;"¶"; ",")))
You can't use an injected parameter with IN; try
s = "
SELECT Distinct targetName
WHERE \"_fk_PanelId\" = ? AND targetId IN (" & Substitute ( RES_Res::_fk_links ; ¶ ; "," ) & ")
ORDER BY targetId
ExecuteSQL ( s ; ";" ; "" ; 4 )
And I suggest you use whitespace and formatting in your calculations.
Hmmm, I've had mixed results with the IN (?). I ended constructing it myself. Seems FileMaker might put '' around it if thinks it needs to be text instead of a number. Then you get ('123, 1234, 1235')
What happens if you just test it with some id's fixed in the IN clause
By the way, have a look at this. If you didn't know about it, you'll buy me a beer if we ever meet, I'm sure :-) It sure saved my day a couple of times.
Before I new this trick I used the fmSQLRunner plugin, it gives the same info.
I tested them and the results are in my screenshot attached.
I know it and use it frequently, but this time it did not give me any info just blank for that customer function.
ok then the sql is syntactically correct :-)
Guess you need to break it down then...
I would change the last AND to and OR, see what happens.
If you get results it's in one of those clauses.
Then test with only the panel and then only the target.
oh and don't forget the required cup of coffee :-)
Try and remove the single quotes. So the IN portion should look like:
IN (123, 1234, 1235)
Thanks for your help, but both gave me syntax error by sql debug customer function (see the attachment for the correct answer added in the original post)
Thank you very much for your help. It did fix the problem and I added the screenshot on my original post. Thanks!.
yes, but only available in the DataViewer. For a way to see what CODE is returned as an error (if any) without the use of DataViewer:
EvaluationError( ExecuteSQL( ... ) )
If 0 is returned, it's valid, so test for that and then proceed.
The CODES are the standard error codes (http://www.filemaker.com/help/14/fmp/en/html/error_codes.html#1030072), except for two that the SQL plug-in developers discovered:
specifically the 8309 & 8310 errors. If I get one of those two, I try to rewrite the query. If in the end I get any error codes > 0 and cannot resolve them, I chalk it up to "unsupported feature(s)" of the ExecuteSQL function.
OK, It was then putting the quotes around the parameter and you don't want that when using the IN . That's why I referenced the article.