11 Replies Latest reply on Apr 6, 2017 5:21 AM by fmpdude

    ExecuteSQL with IN

    DavidZakary

      Having issues with an ExecuteSQL statement.

       

      I have a user created list of keywords. I need to find the keywords in another table which will have a short description and a numeric value. I need to search the description for the keywords and return both the numeric value and the description.

       

      I can't seem to get the WHERE clause with IN working properly.

       

      ExecuteSQL ( "

           SELECT nc_class, nc_class_description

           FROM NICE_data

           WHERE currently_pulling_hair_out IN frustration

      ";

      "";

      "";

      keyword )

       

      What would the proper WHERE statement be?

        • 1. Re: ExecuteSQL with IN
          fmpdude

          Try this ...

          SQL IN Operator

          • 2. Re: ExecuteSQL with IN
            DavidZakary

            That's where I had started and wasn't having much luck. I might use the keyword surrounded by % wildcards and use LIKE instead of IN. Not sure if there's a performance difference between the two.

            • 3. Re: ExecuteSQL with IN
              Vincent_L

              ExecuteSQL ( "

                   SELECT nc_class, nc_class_description

                   FROM NICE_data

                   WHERE currently_pulling_hair_out IN (?)

              ";

              "";

              "";

              keyword )

              • 4. Re: ExecuteSQL with IN
                beverly

                DZ you might get the IN from another query

                WHERE xyz IN (SELECT abc FROM ghi WHERE n = ? )

                As long as it's the same data type it might work.

                 

                Sent from miPhone

                • 5. Re: ExecuteSQL with IN
                  philmodjunk

                  In assumes that you have a set of values. I think you are trying to do the type of search where, if this were a FileMaker Find operator, you'd be using the * wild card such as:

                   

                  *Frustration* in your find request.

                   

                  If I am right you need to use the Like keyword, not In and use the % operator the way that you would use * in a FileMaker Find request.

                   

                  Keep in mind that text comparisons are case sensitive so how you capitalize makes a difference as well.

                   

                  So you might need something like:

                   

                  WHERE

                       Upper ( field ) LIKE %'SEARCHTEXTHERE'%

                  • 6. Re: ExecuteSQL with IN
                    DavidZakary

                    Using the keyword surrounded by %'s seems to work fairly well.

                     

                    Thanks all.

                    • 7. Re: ExecuteSQL with IN
                      beverly

                      does that actually work? you might consider passing the parameter:

                      WHERE abc LIKE %?%

                      ...

                      ; "keyword"

                      so that the proper quoting happens and it's now dynamic.

                      • 8. Re: ExecuteSQL with IN
                        user19752

                        % should be in quote, so

                        LIKE ?

                        ...

                        "%keyword%"

                        • 9. Re: ExecuteSQL with IN
                          fmpdude

                          Yes, using LIKE in FMP queries will give you an noticeable (negative) performance hit.

                           

                          Avoid LIKE, if you can, with FMP's SQL.

                           

                          You do not need LIKE for IN to work (unless you need to wildcard your query).

                           

                          Examples...

                           

                          I have a home database where I track all the vendors who have ever done work. If I want to search for those vendors where the keywords have "tree" or "Gas" (words by themselves in the keywords field), I can do this query:

                           

                          select vendor, keywords, vendor_rating from vendors where KEYWORDS in ('tree', 'Gas')

                           

                          However, if I want to find all the vendors where "cooling" (as in air conditioning) is anywhere in the keywords field, I could do this (no IN needed):

                           

                          select vendor, keywords, vendor_rating from vendors where UPPER(KEYWORDS) LIKE UPPER('%Contractor%')

                           

                          (above I did an UPPER on both fields to make sure there were no case-sensitive issues -- LIKE IS case sensitive.)

                           

                          You can also do a sub-query with "IN" as beverly showed you above. Sub-queries are a typical way to do an "IN" clause.

                           

                          select * from ORDERS a where MAILER_STATE= 'CA' and mailer_state in (Select MAILER_STATE from ORDERS)

                           

                          (Note that in the query above, the sub-query is completely unnecessary (you wouldn't use it), since the first part of the query does all you need in this case. Typically a sub-query would use values from another table. This was just a simple example, however.)

                           

                          -------

                           

                          For a more realistic example where you might use "IN", consider you have two tables:

                           

                          1. TestSmaller

                          2. Test

                           

                          Each table has belt colors as you might have in martial arts training.

                           

                          TestSmaller table has these records:

                           

                          Test table has these records:

                          Consider the following IN query:

                           

                          select TestSmaller.belt from testsmaller where belt in (select belt from test)

                           

                          Result?

                          --

                           

                          HOPE THIS HELPS.

                          • 10. Re: ExecuteSQL with IN
                            Vincent_L

                            Note that sub queries are unfortunately awfully slow in Filemaker's SQL. It seems they're evaluated for each records, which is crazy in most case, where the sub query doesn't depend on a record's filed value).

                            So you're much better if you do the subquery first and replace it as an IN in the main query

                            • 11. Re: ExecuteSQL with IN
                              fmpdude

                              Agreed, it really depends how much data you have.  The queries I posted above all worked relatively quickly, that is, "acceptably fast". However, these queries were against tables with only a hundred or so rows. If you have a lot more data, however, be aware.

                               

                              For example, a very simple LIKE query in FMP against 500,000 records (a table with a single column) takes over 5 seconds, where in MySQL it's 0.22 seconds. Thus for a simple LIKE query, the peformance percent difference is staggering (MySQL is 21 times, or 2,100% faster!). Whether that performance difference makes any difference for a single query would be subjective. But, overall, for my potential projects, SQL performance in FMP is always (currently) a show-stopper. Consider the case where you have multiple users executing queries and they're waiting for over five seconds -- as opposed to nearly instant response. Not good, right?

                               

                               

                               

                              Hopefully in a some future version of FMP, SQL won't be the "bolted-on" feature it now seems to be and will support UPDATE, INSERT, and DELETE as you would expect in any environment that supports SQL.