14 Replies Latest reply on Jan 4, 2017 9:55 AM by philmodjunk

    IsEmpty statement within an ExecuteSQL?

    ezeitgeist

      I have been using an SQL Calculation for a field that has been working well. I just ran into a situation when one of the fields in the SQL equation needs to change based on if one field is empty. Here is a more specific reference:

       

      ExecuteSQL ( "SELECT COUNT ( CountRef ) FROM \"Connections\" WHERE \"UserID\"=? AND \"Booked it\"=? AND IT_Audition=? AND \"Top worn Callback\"=?" ; "" ; "" ; $$UserID ; 1 ; 1 ; "dress" )

       

      The situation is that IF the "Top worn Callback" field is EMPTY, then I want it INSTEAD to use the "Top worn" field (they are two different fields in the "Connections" table). Both the "Top worn Callback" and "Top worn" field would still have the same item it is searching (in this case "dress").

       

      How do I add the "IsEmpty" aspect into the ExecuteSQL? Would I replace \"Top worn Callback\" with ( IsEmpty ( Connections::Top worn Callback ; "\"Top worn\"" ; "\"Top worn Callback\"") ) ?

        • 1. Re: IfEmpty statement within an ExecuteSQL?
          philmodjunk

          FieldNameHere Is Null

           

          is the SQL equivalent of IsEmpty()

          1 of 1 people found this helpful
          • 2. Re: IsEmpty statement within an ExecuteSQL?
            ezeitgeist

            Phil, so I should replace with?:

             

            ( FieldNameHere Is Null ( Connections::Top worn Callback ; \"Top worn\" ; \"Top worn Callback\") )

            • 3. Re: IsEmpty statement within an ExecuteSQL?
              erolst

              I think you can use

               

              ...

                AND ( \"Top worn callback\" = ? OR \"Top worn\" = ? )

                " ; "" ; "" ; $$userID ; 1 ; 1 ; "dress" ; "dress"

              )

               

              Note the parentheses around the OR and the additional parameter.

              1 of 1 people found this helpful
              • 4. Re: IsEmpty statement within an ExecuteSQL?
                ezeitgeist

                Correction:

                 

                ( Connections::Top worn Callback Is Null ; \"Top worn\" ; \"Top worn Callback\")

                • 5. Re: IsEmpty statement within an ExecuteSQL?
                  ezeitgeist

                  erolst: There is a chance that "Top worn" could be "dress" and then "Top worn Callback" could be "blouse" and I'd need it to ONLY register "blouse" in that case. I think the OR would count the Top worn dress even if Top worn Callback was not empty. Am I correct in that?

                  • 6. Re: IsEmpty statement within an ExecuteSQL?
                    philmodjunk

                    No. It's not a function. It's a defined word that enables the comparison expression to evaluate correctly.

                     

                    I think what you want would read like this unless you have misunderstood the purpose of a WHERE clause.

                     

                    ( \"Top worn Callback\" = ? or ( \"Top worn Callback\"is Null AND \"Top worn\"= ? ) )

                     

                    note that you will have to repeat the same field twice in the list of optional parameters, once for the first ? and once for the second ?.

                    1 of 1 people found this helpful
                    • 7. Re: IsEmpty statement within an ExecuteSQL?
                      taylorsharpe

                      Let ( [

                       

                      SQL = "SELECT

                                     COUNT ( CountRef )

                                FROM

                                     \"Connections\"

                                WHERE

                                     \"UserID\" = ? AND

                                     \"Booked it\" = ? AND

                                     IT_Audition = ? AND

                                     \"Top worn Callback\" IS NULL" ;

                      Result = ExecuteSQL ( SQL ; Char ( 9) ; ¶ ; $$UserID ; 1 ; 1 )

                       

                      ] ;  Result )

                       

                       

                       

                       

                      What is CountRef?  If it is a primary key, the use COUNT ( * ) instead because that is performed more quickly at least in FileMaker SQL. 

                      1 of 1 people found this helpful
                      • 8. Re: IsEmpty statement within an ExecuteSQL?
                        ezeitgeist

                        Phil: I am pretty new to ExecuteSQL so definitely still learning and trying to fully grasp each aspect (ie. I may not fully understand the WHERE properly, which is causing me a slower learning curve).

                         

                        ExecuteSQL ( "SELECT COUNT ( CountRef ) FROM \"Connections\" WHERE \"UserID\"=? AND \"Booked it\"=? AND IT_Audition=? AND ( \"Top worn Callback\"=? OR ( \"Top worn Callback\" Is Null AND \"Top worn\"=? ) ) ; "" ; "" ; $$UserID ; 1 ; 1 ; "dress" ; "dress" )

                         

                        I get a "the specified field cannot be found" error.

                        • 9. Re: IsEmpty statement within an ExecuteSQL?
                          ezeitgeist

                          Taylor: I do not know the Count(*) command. Yes, the CountRef literally is a "1" in a field for every record.

                          • 11. Re: IsEmpty statement within an ExecuteSQL?
                            taylorsharpe

                            ezeitgeist wrote:

                             

                            Taylor: I do not know the Count(*) command. Yes, the CountRef literally is a "1" in a field for every record.

                             

                            The Count ( * )  where you use the asterisk instead of a field name basically counts each record found and it looks at whole records instead of fields.  If the field sometimes has something in it and sometimes not, then the count would only count ones in the found set that have something in them.  But if you're searching on a field that always has something in it, then use the Count ( * ) instead.  FileMaker somehow evaluates counting whole records faster than it does looking at contents in individual fields.  So using the Count ( * ) will significantly improve the speed that FileMaker counts this for you.  It is just one of those weird things about how FileMaker works that isn't necessarily intuitive.

                             

                             

                             

                            Let ( [

                             

                            SQL = "SELECT

                                           COUNT ( * )

                                      FROM

                                           Connections

                                      WHERE

                                           UserID = ? AND

                                           \"Booked it\" = ? AND

                                           IT_Audition = ? AND

                                           \"Top worn Callback\" IS NULL" ;

                            Result = ExecuteSQL ( SQL ; Char ( 9) ; ¶ ; $$UserID ; 1 ; 1 )

                             

                            ] ;  Result )

                            1 of 1 people found this helpful
                            • 12. Re: IsEmpty statement within an ExecuteSQL?
                              erolst

                              Reading and debugging is much easier (for us, too!) if you apply formatting and whitespace:

                               

                              ExecuteSQL ( "

                                 SELECT COUNT ( CountRef )

                               

                                FROM \"Connections\"

                               

                                WHERE

                                  \"UserID\" = ? AND

                                  \"Booked it\" = ? AND

                                  IT_Audition = ? AND

                                  ( \"Top worn Callback\"=? OR ( \"Top worn Callback\" Is Null AND \"Top worn\"=? ) )

                               

                                <missing quote> ; "" ; "" ; $$UserID ; 1 ; 1 ; "dress" ; "dress"

                              )

                               

                               

                              which lets you easily discover that the closing quote of the SQL query string is missing.

                              2 of 2 people found this helpful
                              • 13. Re: IsEmpty statement within an ExecuteSQL?
                                ezeitgeist

                                Erolst: YES! I was actually looking and realized I was missing a quote but couldn't figure out the correct location to place it. Thank you!

                                Phil: Thank you!

                                Taylor: Thank you on the COUNT(*) education!

                                • 14. Re: IsEmpty statement within an ExecuteSQL?
                                  philmodjunk

                                  If you are interested in better use of this function, you should also consider methods that use this function but avoid explicitly listing field and table occurrence names as part of the quoted string. Doing that produces a query that can fail in the future should the wrong field or table occurrence name be changed.

                                   

                                  These methods can create much more complex expressions, but the added durability/flexibility is worth the effort.

                                   

                                  For a tool I use to make this happen, see:

                                   

                                  DIGFM: How Brittle is Your Database? (11/10/2016; Santa Clara, CA)

                                   

                                  There's a demo file in that thread where you can enter a simple SQL like expression (but without explicit table occurrence and field names) and a system of custom functions expands the query out into something that you can easily edit to produce a query expression that both contains an easy to read SQL query yet avoids quoted names in the expression. It does require that you import a custom function, SQLRef, from the demo file into your solution in order for it to evaluate correctly.