AnsweredAssumed Answered

FM ExecuteSQL syntax problem with 'IN()' clause

Question asked by disabled_JustinClose on Jun 9, 2015
Latest reply on Jun 10, 2015 by disabled_menno

Title

FM ExecuteSQL syntax problem with 'IN()' clause

Post

I'm having a syntax problem getting the 'IN' clause to work in FM's ExecuteSQL statement.  I would like to use a '?' placeholder in the statement (just looks cleaner), but when I do it doesn't work.  I already have a string that contains the list of numbers (this is all numbers...except the fact that it is a string of numbers :)  ).  I want to use that variable in this statement.

Here's a WORKING example:

Let ( [
    ids = "281,282,283";
    sq =
"SELECT  ID_fk, sum(Amount)  FROM Payments  WHERE ID_fk  IN ( " & ids & " ) GROUP BY ID_fk"
] ; ExecuteSQL ( sq ; "" ; ""  ) )

But it's not using the '?' placeholder.  What I would like to do instead:

Let ( [
    ids = "281,282,283";
    sq =
"SELECT  ID_fk, sum(Amount)  FROM Payments  WHERE ID_fk  IN ( ? ) GROUP BY ID_fk"
] ; ExecuteSQL ( sq ; "" ; ""  ; ids ) )

I found some custom functions from folks that help format this correctly, but I would rather not use a custom function for just this one need (only doing this once).

Thanks,

--  Justin

Outcomes