4 Replies Latest reply on Jul 20, 2016 10:47 PM by glorifindal

    SQL Syntax Error

    glorifindal

      Hi there Community,

       

      I have been trying to use the SQL IN operator.

      I'm not even sure if it's a good way to use in Filemaker, perhaps someone could tell me if not.

       

      However, could someone PLEASE tell me what is wrong with the following query.

       

      _val below is actually the results of a variable

      It contains a list of ids, which I substituted out to give the  format below.

      Of course, it could very well be the format of the variable which is wrong.

       

      [quote]

      Let([

      _val = "('9B0636E5-ABBA-4BCC-A173-D578FEFF7ABD','5F0C33FD-DC23-44EF-B87F-E4CF5D306D86','D43844EA-7800-4470-AECE-329732D1A962')" ;

       

      $_query = "SELECT \"Component Name\" FROM SQLABB WHERE id IN ?" ;

      field.sep = "" ;

      row.sep = "" ;

      $_result = ExecuteSQL( $_query; field.sep ; row.sep ; _val )

      ] ;

      ""

      )

        • 1. Re: SQL Syntax Error
          wimdecorte

          You can't use the "?" placeholder in that way with an IN clause.

           

          If you want to use the "?" placeholder then your query should look like this:

           

          $_query = "SELECT \"Component Name\" FROM SQLABB WHERE id IN (?,?)" ;

           

          And you subsitute in the individual list members.

          Obviously if you have an unknown number of entries in the IN clause then it because a bit harder to dynamically create your query syntax.  But not impossible.

          • 2. Re: SQL Syntax Error
            glorifindal

            Aha,

             

            many thanks indeed Wim,

             

            so I just need to concatenate the values into the query ...

             

            Many thanks indeed for your swift response

             

            kindest

             

            G

            • 3. Re: SQL Syntax Error
              beverly

              There are a few "tricks" to using IN. Wim pointed out one (each value gets a "?")

              You can use variables, but do not put the "( ... )" into the variable:

               

              $var = "1,2,4,3,6"

               

              "WHERE abc IN ( ? )"

              or

              "WHERE abc IN( " & $var & ")" // my preference

               

              the deal is that FM/ExecuteSQL will determine if the value of the parameter is a number and NOT quote when evaluated

              or single quote the value if text when evaluated.

              so, obviously, it has trouble with a string of values (comma-delimited as needed for the IN part of the clause).

              thus my preference is to use the $var as a concat and I handle what gets single quotes or not.

               

              this is easy if the values are hard-coded. it gets trickier when it's dynamically-build list of values to be used with IN.

               

              Another way to handle:

               

              "WHERE abc IN ( SELECT .... )" // A nested select to get the values. this may or not work for you.

              so I still prefer to get the values and use them as stated above.

               

              A few articles that may help:

               

              read the comments, too!

               

              (there are links to other articles.

               

              beverly

              1 of 1 people found this helpful
              • 4. Re: SQL Syntax Error
                glorifindal

                Thanks very much Beverly - the articles are a great help.