4 Replies Latest reply on Sep 27, 2014 5:25 AM by rwu2359

    ExecuteSQL, Coalesce and "As" (Alias) - Not Working

    Doug Staubach

      Title

      ExecuteSQL, Coalesce and "As" (Alias) - Not Working

      Post

      Hello:

      I'm using COALESCE to choose the first non-null value in a list of ExecuteSQL fields.

      My problem is that I need to "match" against the resulting (combined) field in a subsequent JOIN, and FileMaker doesn't seem to recognize the AS clause for naming the resulting field. (The error message that I am receiving is: The column named "EmployeeName" does not exist in table "PP1"). -- Note that the formula doesn't work when using the (first) actual field name is used either, so I'm at a loss.

      Is this a bug?

      Thanks,

      DougNdenver

       

      Note - Sample of People (Table) is attached as an image below.

      Formula:

      ExecuteSQL ( "

      SELECT
            PP1.EmployeeID
           ,COALESCE ( PP1.LegalName, PP1.PreferredName) AS EmployeeNameFROM
            People As PP1
         JOIN
            People As PP2
         ON
            PP1.EmployeeName = PP2.NameOfManager
      " ; "" ; "" )

       

      Expected Result (in display order):

           52,Janet Jones
           13,Andrew Archer
           48,Bob Billings

       

      People_%28Table%29.png