10 Replies Latest reply on Sep 2, 2014 9:22 AM by taylorsharpe

    ExecuteSQL

    garywatson

      This should be simple but I am new to ExecuteSQL calculations.

       

      I have a field "EmailGroup"

      It is on a table called "Contacts"

      I want to know how many records contain the text "FreeTicket" in the field "EmailGroup"

        • 1. Re: ExecuteSQL
          erolst

          Try

           

          ExecuteSQL ( "

            SELECT COUNT (*)

            FROM Contacts

            WHERE EmailGroup LIKE '%FreeTicket%'

            " ; "" ; ""

          )

          how many records contain the text "FreeTicket" in the field "EmailGroup"

          If the field not just contains, but IS EQUAL to the value "FreeTicket", then this works faster:

           

          ExecuteSQL ( "

            SELECT COUNT (*)

            FROM Contacts

            WHERE EmailGroup = ?

            " ; "" ; "" ; "FreeTicket"

          )

          • 2. Re: ExecuteSQL
            taylorsharpe

            Formatting doesn't matter for functionality or performance, but I like to think the way I do it is more readable.  It is totally a preference way to do things:

             

             

             

            Let ( [

             

            SQL =  "SELECT

                             COUNT ( * )

                     FROM

                           Contacts

                     WHERE

                           EmailGroup LIKE ?" ;

            Result = ExecuteSQL ( SQL ; ¶ ; ¶ ; "%FreeTicket%" )

             

            ] ;

             

            Result

             

            )

            • 3. Re: ExecuteSQL
              Malcolm

              It really is a preference thing   To my eyes, erolst's code is much clearer than yours.

               

              malcolm

              • 4. Re: ExecuteSQL
                beverly

                not agreeing or disagreeing with the "formatting" issue.

                 

                But the use of the "%" wild card and LIKE can be a killer (stored or unstored field) in time needed. The usage here is correct when needing "contains".

                 

                Is there a reason it needs to be ExecuteSQL?

                 

                If it can be a calculated (auto-enter perhaps) with:

                     IF ( PatternCount ( Contacts::EmailGroup ; "Free ticket" ) ; 1 ; 0 )

                then it could be used with a found set of records, too.

                 

                Just wondering...

                Beverly

                • 5. Re: ExecuteSQL
                  taylorsharpe

                  I guess it is just a preference thing on what you get used to and Malcolm and Erolst prefer differently.  I was just pointing out there are a lot of ways of formatting ExecuteSQL.  Why I like my method is that I often put double forward slashes at the end of each line and explain what I am doing.  I'm big on commenting even formulas when possible. 

                   

                  I like Beverly's comment too.  ExecuteSQL is usually not any faster than normal FileMaker steps.  When it is good is when you can use it to avoid certain other script steps that do take a long time such as going to another layout to get information.  I run into some solutions where they try to use it too much when that often is not best. 

                  • 6. Re: ExecuteSQL
                    beverly

                    As for formatting....

                     

                         I use erolst's method when it's ONE field, ONE table, ONE criteria, then I use taylor's method when it's MULTIPLE and a list is easy to read. I also use this:

                     

                         SELECT

                              field1

                              , field 2

                              , field 3

                     

                    ....

                     

                    NOTE the comma at the start of a line? I can't tell you the number of times a query failed, because of a missing comma! (FM's eSQL and the big guns SQL).

                    this makes it easy to see where I've made that mistake. I also can "comment out" any line I wanted once, but don't need now and don't want to delete altogether:

                     

                         SELECT

                              field1

                              //, field2

                              , field3

                              , field4

                     

                    Is one method better than another, NO!!  Take what you want and ignore the rest.

                     

                    Beverly

                    • 7. Re: ExecuteSQL
                      taylorsharpe

                      Hmmm... I never thought about beginning a line with a comma since that is not normally intuitive, but I can see how works great for things like commenting out lines.  Might have to change the way I do things!  <grin>

                      • 8. Re: ExecuteSQL
                        beverly

                        I use the same method in my Let() statements:

                         

                             Let (

                                  [ exp1

                                  ; exp2

                                  ; exp3

                                  ]; result

                             ) // comment here

                         

                        It makes it easy to see the missing separators. But that's me.

                        • 9. Re: ExecuteSQL
                          erolst

                          At least Filemaker syntax doesn't feature curly braces (yet) and the religious discussions that go with them …

                           

                          Maybe let's agree that the way you format your calculations is your personal style, but that applying formatting is a common best practice.

                          • 10. Re: ExecuteSQL
                            taylorsharpe

                            I agree with erolst.  But I encourage that whatever style you do, that you're consistent throughout the solution.  Don't jump between formatting styles!

                             

                            And I highly encourage people to add lots of comments in scripts and calculations to explain what you are doing.