2 Replies Latest reply on Jun 10, 2015 2:38 PM by disabled_menno

    FM ExecuteSQL syntax problem with 'IN()' clause

      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

        • 1. Re: FM ExecuteSQL syntax problem with 'IN()' clause
          philmodjunk

          Good luck and I'll read any other responses closely, but when I tried this a while back, I only got the first approach to work myself.

          I suspect that it has to do with how the optional parameter widgit works. You can use it to insert a value, but not an expression apparently.

          • 2. Re: FM ExecuteSQL syntax problem with 'IN()' clause

            As you found out yourself and as Phil stated, only the first of your 2 approaches will work. There is another approach that works too:

            Let([
                  val = SomeValue ; 
                  sql1 = "SELECT FieldID FROM SomeTable WHERE SomeField = ?" ; 
                  sql2 = "SELECT OtherField FROM SomeOtherTable WHERE OtherFieldID IN (" & sql1 & ")"
            ] ;
                  ExecuteSQL ( sql2 ; ""  ; "" ; val )
            )

            Here another query is nested into the IN-clause. The list you use is probably a result from a find or something like that, so you can query for that too. My experience with this approach though is that this get slow pretty fast, but for a couple of hundreds or maybe a few thousand records it works fine.