9 Replies Latest reply on Oct 12, 2012 10:19 PM by MjtBiz

    ExecuteSQL function and is Null vs ''

    MjtBiz

      One of the things I was looking forward to with the ExecuteSQL function was getting around the issue with empty values in compound keys. In a relationship, if I have three values that establish my key and one of them might be what Filemaker considers empty, the relationship will not find a match for those records even if the match field on the other side of the relationship is also empty. There are tricks around this, but they just add more complexity to the solution.

       

      So with ExecuteSQL, I figured that I could not only cut down on many of those relationships on the graph, but also deal with the empty values issue. I write my select statement:

       

      ExecuteSQL(

      "SELECT T2.MyField

      FROM Table2 as T2

      WHERE T2.FieldA ='" & LocalFieldA & "' AND

      T2.FieldB ='" & LocalFieldB & "' AND

      T2.FieldC ='" & LocalFieldC & "';

      ",";

      "|"

      )

       

      The problem is that when LocalFieldA, B or C is empty, FileMaker does not recognize the empty string [''], so the select statement fails on the match. It does, however, recognize the NULL value, so I can wrap every condition with a Case/If statement:

       

      ExecuteSQL(

      "SELECT T2.MyField

      FROM Table2 as T2

      WHERE " & Case( isempty( LocalFieldA ), "T2.FieldA is NULL"; "T2.FieldA ='" & LocalFieldA & "'" ) & " AND "

      & Case( isempty( LocalFieldB ), "T2.FieldB is NULL"; "T2.FieldB ='" & LocalFieldB & "'" ) & " AND "
      & Case( isempty( LocalFieldC ), "T2.FieldC is NULL"; "T2.FieldC ='" & LocalFieldC & "'" ) & " AND "

      ",";

      "|"

      )


      But now I am back to more complexity and less readable code.

       

      I hope I am missing something here and that one of you geniuses has a better workaround.

       

      Thanks,

       

      Michael Thompson

        • 1. Re: ExecuteSQL function and is Null vs ''
          Malcolm

          One of the things I was looking forward to with the ExecuteSQL function was getting around the issue with empty values in compound keys.  In a relationship, if I have three values that establish my key and one of them might be what Filemaker considers empty, the relationship will not find a match for those records even if the match field on the other side of the relationship is also empty.  There are tricks around this, but they just add more complexity to the solution.

           

          So with ExecuteSQL, I figured that I could not only cut down on many of those relationships on the graph, but also deal with the empty values issue.

           

          The problem is that when LocalFieldA, B or C is empty, FileMaker does not recognize the empty string , so the select statement fails on the match.  It does, however, recognize the NULL value, so I can wrap every condition with a Case/If statement:

           

          But now I am back to more complexity and less readable code.

           

          I hope I am missing something here and that one of you geniuses has a better workaround.

           

          How about adding a bit more complexity? If you create a custom function to handle the creation of the case statement you can have code complexity hidden behind the simplicity of the function call, see the mockup below.

           

           

          ExecuteSQL(

           

          "SELECT T2.MyField

           

          FROM Table2 as T2

           

          WHERE "   & sql_compare("T2.FieldA" ; LocalFieldA ) & " AND "

          WHERE "   & sql_compare"(T2.FieldB" ; LocalFieldC ) & " AND "

          WHERE "   & sql_compare("T2.FieldC" ; LocalFieldD ) & " AND "

           

          ",";

           

          "|"

           

          )

           

           

          Malcolm

          • 2. Re: ExecuteSQL function and is Null vs ''
            MjtBiz

            Malcom,

             

            Thanks for your input.

             

            I was thinking about a custom function, and you are correct that it would make the code easier on the eyes.  I may go in this direction, and, if so, I will share it with the Forum.  I am hopeful for other input and would welcome other things that could be addressed with such a function.

             

            Regards,

             

            Michael

            • 3. Re: ExecuteSQL function and is Null vs ''
              gwinzeler

              Why do you have empty fields for keys?

              GARY

              • 4. Re: ExecuteSQL function and is Null vs ''
                beverly

                Gary, rather than "empty" keys, I believe the OP had three possible matches. Or rather, matches if any of three keys matched.

                 

                ABC

                AB

                AC

                BC

                (and possibly

                A

                B

                C)

                 

                With SQL the OR can be used in the query.

                 

                WHERE ....

                    OR ...

                    ...

                 

                In FM Relationships, there are multi-line keys (but I don't know if these were/are used).

                 

                -- sent from my iPhone4 --

                Beverly Voth

                --

                • 5. Re: ExecuteSQL function and is Null vs ''
                  MjtBiz

                  Beverly,

                   

                  Thanks for chiming in on this, I hope you are well.

                   

                  Duh!  Of course this is just the thing I was looking for. 

                   

                  WHERE     ( T2.FieldA is NULL or T2.FieldA ='" & LocalFieldA & "'" ) & AND

                            ( T2.FieldB is NULL or T2.FieldB ='" & LocalFieldB & "'" ) & AND

                            ( T2.FieldC is NULL or T2.FieldC ='" & LocalFieldC & "'" );

                   

                  I think that will work.  And it is more readable and not overly complex.  Great solution!

                   

                  Michael

                  • 6. Re: ExecuteSQL function and is Null vs ''
                    nsabel

                    FileMaker does not recognize the empty string ['']

                    First this is not just a Filemaker thing its in the SQL and happens in non filemaker systems as well.

                     

                    Second why do you have a NULL as a key this makes no sense unless this is a foreign key and no relationship exists for the record.

                     

                    Third I agree your best option is a custom functions. This reduces the "complexity" the same way using a built in feature would. Do magic logic doesnt exist the only option is either Filemaker creates the "complex" function for you and hides it or you have to do it yourself.

                     

                    Last I recommend you use the parameters as your SQL statement allows for injection and is a security risk. You could also do all of this in the SQL statement without the addtional filemaker logic. However this is still "complex" and may be even less readable depending on your knowledge of SQL.

                     

                    Nick

                    • 7. Re: ExecuteSQL function and is Null vs ''
                      beverly

                      Michael, I don't know what you consider a "match" any of the three or at least two of the three. See if this helps

                      ExecuteSQL (
                           SELECT T2.MyField
                           FROM Table2 AS T2
                           WHERE T2.fieldA = ? 
                                OR T2.fieldB = ?
                                OR T2.fieldC = ?
                      
                      "," ; "|" ; LocalFieldA ; LocalFieldB ; LocalFieldC )
                      
                      You can combine AND with OR and put parenthesis where needed.
                           WHERE ( x = 1 OR y = 2 ) AND z = 3 )

                       

                      You can rewrite your "CASE" to be all in SQL-speak, but I think you want the OR instead of AND (or a combo)

                      Beverly

                       

                      ExecuteSQL(

                       

                      "SELECT T2.MyField

                       

                      FROM Table2 as T2

                       

                      WHERE     T2.FieldA ='" & LocalFieldA & "' AND

                       

                                T2.FieldB ='" & LocalFieldB & "' AND

                       

                                T2.FieldC ='" & LocalFieldC & "';

                       

                      ",";

                       

                      "|"

                       

                      )

                       

                      • 8. Re: ExecuteSQL function and is Null vs ''
                        beverly

                        Hey, MT!

                        is NULL AND is NULL AND is NULL (a possibility) will net you records if all three are empty, if that ok?

                         

                        Beverly

                        • 9. Re: ExecuteSQL function and is Null vs ''
                          MjtBiz

                          Bev,

                           

                          Thanks again for your input.  To clarify for the thread:  what I am doing here is working with a non-FileMaker data structure overwhich I have no control.  So the possiblitity of blank elements of the key is inherited, I am just working around what already exists.  In most cases, I am working with 3 to 6 key elements, only one or two of which will ever be NULL, so there are always some values in the key.

                           

                          I think I am going to work on this a bit, but I think I am much clearer now of my options.

                           

                          Thanks to all for the help.

                           

                          Michael