9 Replies Latest reply on Nov 1, 2013 3:05 AM by CICT

    ExecuteSQL Query Result With Null Values

    CICT

      Further to my ExecuteSQL Speed Improvements posting, I've another example that seems to me to be an obvious requirement, but I've now spent too much time on Google, and failed to spot a solution, and welcome Technet advice.

       

      Example data only below to keep it as simple as possible, in our actual query we are using || concantentation within our results to add information to the values.

       

      Example values from MyTable:

       

      FirstName MiddleName Surname

      Tom Campbell Smith

      Dick Jones

      Freda MacDonald Baker

       

      By running the following:

      Let (

      sql =

      "SELECT m.FirstName,

      CASE WHEN m.MiddleName IS NULL THEN '' ELSE m.MiddleName END,

      m.Surname

      FROM MyTable m"

      ;

      ExecuteSQL ( sql ; "¶" ; "" )

      )

       

      Results in:

      Tom

      Campbell

      Smith

       

      Dick

       

      Jones

       

      Freda

      MacDonald

      Baker

       

      Very simply, we need to suppress the blank row listed between 'Dick' and 'Jones' where the MiddleName value is null

       

      The same results could have been achieved by using:

      CASE WHEN m.MiddleName IS NOT NULL THEN m.MiddleName END

       

      I don't believe the WHERE FROM statements are relevant here, as in our actual query we are pulling data from many locations and the problem is dependent on the values returned, not the selection criteria.

       

      We can achieve our desired result using FileMaker's substitute function:

       

      Let (

      sql =

      "SELECT m.FirstName,

      CASE WHEN m.MiddleName IS NULL THEN '|' ELSE m.MiddleName END,

      m.LastName

      FROM MyTable m"

      ;

      Substitute ( ExecuteSQL ( sql ; "¶" ; "" ) ; "|¶" ; "" )

      )

      (the pipe probably not the best character to use)

       

      Which will return the desired result:

      Tom

      Campbell

      Smith

       

      Dick

      Jones

       

      Freda

      MacDonald

      Baker

       

       

      However, it would be nice to do this directly within SQL if possible.

       

      Andy

        • 1. Re: ExecuteSQL Query Result With Null Values
          TimDietrich

          Andy --

           

          Here's another approach, which supresses the NULL middle name rows:

           

          Let (

               sql =

               "SELECT

                                      m.FirstName || '¶' ||

                                      CASE WHEN m.MiddleName IS NOT NULL THEN m.MiddleName || '¶' END ||

                                      m.Surname || '¶'

               FROM MyTable m"

          ;

               ExecuteSQL ( sql ; "" ; "¶" )

          )

           

          -- Tim

          • 2. Re: ExecuteSQL Query Result With Null Values
            CICT

            Hi Tim

             

            This is very helpful and it works without the need of any substitutions, being much neater than my original post.

             

            Both approaches are susceptible to a user leaving a trailing return to the data, but so is every report and we could trap for this with script triggers.

             

            My only reservation is that it is again relying on FileMaker's execution of the SQL query and was wondering whether we can achieve this natively within the query?

             

            Appreciate your input

             

            Andy

            • 3. Re: ExecuteSQL Query Result With Null Values
              TimDietrich

              Andy --

               

              You wrote: "My only reservation is that it is again relying on FileMaker's execution of the SQL query and was wondering whether we can achieve this natively within the query?"

               

              Do you mean that the query is dependant upon FileMaker's implementation of SQL? If so, then I think it is (for the most part anyway) ANSI SQL compliant -- including the CASE statement and the concatentation operator.

               

              -- Tim

              • 4. Re: ExecuteSQL Query Result With Null Values
                CICT

                Hi Tim

                 

                You beat me to it! I was intending to reply again as I have been thinking about this more and do agree with you. My first reaction was that it did rely on FileMaker's execution, but on reflection and spending a bit more time with it, the concatenation technique is pure SQL.

                 

                It is this type of information we find difficult to find as our use of ExecuteSQL expands and I hope this posting helps others.

                 

                It will be interesting to see whether any alternative approaches get posted.

                 

                Again, thanks for your input and all the best

                 

                Andy

                • 5. Re: ExecuteSQL Query Result With Null Values
                  TimDietrich

                  Andy --

                   

                  I'm glad I could help.

                   

                  Another approach might be this...

                   

                  Let (

                            sql =

                            "

                  SELECT

                            m.FirstName

                  FROM MyTable m

                  UNION

                  SELECT

                            m.MiddleName

                  FROM MyTable m

                  WHERE

                            m.MiddleName IS NOT NULL

                  UNION

                  SELECT

                            m.Surname

                  FROM MyTable m

                  "

                   

                   

                  ;

                            ExecuteSQL ( sql ; "" ; "¶" )

                  )

                   

                  That gives a similar result. However, it lacks the extra carriage return between individual "people" records.

                   

                  It will be interesting to see if any other solutions are suggested.

                   

                  -- Tim

                  • 6. Re: ExecuteSQL Query Result With Null Values
                    beverly

                    The is precisely what 'coalesce()' should do. Please research it.

                     

                    -- sent from my iPhone4 --

                    Beverly Voth

                    --

                    • 7. Re: ExecuteSQL Query Result With Null Values
                      CICT

                      Hi Beverly

                       

                      I had looked at COALESCE () before and have revisited it since your post. I thought you'd cracked it and posted accordingly, but have had to repost as I still have the original problem:

                       

                      Certainly:

                      Let (

                      sql =

                      "SELECT m.FirstName + COALESCE ( m.MiddleName , '' )  + m.Surname

                      FROM MyTable m"

                      ;

                      ExecuteSQL ( sql ; "" ; "" )

                      )

                       

                      Results in:

                      TomCampbellSmith

                      DickJones

                      FredaMacDonaldBaker

                       

                      Which is getting towards where I want to get. However, if either Tim's concatentation or comma separators are introduced, e.g:

                      Let (

                      sql =

                      "SELECT m.FirstName || '¶' || COALESCE ( m.MiddleName , '' )  || '¶' ||  m.Surname || '¶'

                      FROM MyTable m"

                      ;

                      ExecuteSQL ( sql ; "" ; "" )

                      )

                       

                      It results in a blank row between Dick and Jones again:

                      Tom

                      Campbell

                      Smith

                       

                      Dick

                       

                      Jones

                       

                      Freda

                      MacDonald

                      Baker

                       

                      It appears that we do need the CASE statement to be able to control whether the pilcrows are added or not. COALESCE on it's own doesn't appear to be able to do this. However, great function for its stated purpose of finding the first non NULL value.

                       

                      Unless someone else can show me how.......

                       

                      Again, thanks for the input.

                       

                      Andy

                      • 8. Re: ExecuteSQL Query Result With Null Values
                        Mike Duncan

                        Maybe just add a little filemaker function to the result of the let statement in order to get rid of the extra returns for you...

                         

                        Substitute ( ExecuteSQL ( sql ; "" ; "" ) ; [ "¶¶" ; "¶" ] )

                        • 9. Re: ExecuteSQL Query Result With Null Values
                          CICT

                          Hi Mike

                           

                          The only problem with this is that you lose the pilcrow between the records, which is why we used the pipe character in the original suggestion.

                           

                          Thanks for your input.

                           

                          Andy