3 Replies Latest reply on Oct 7, 2014 1:32 PM by TSGal

    ExecuteSQL, COALESCE, and "As" (Field Alias) - Does not work

    Doug Staubach

      Summary

      ExecuteSQL, COALESCE, and "As" (Field Alias) - Does not work

      Product

      FileMaker Pro

      Version

      13

      Operating system version

      OSX 10.9

      Description of the issue

      When using CASE in a SELECT function, the resulting (virtual) column should be accessible by using a column alias, but this capability seems to be broken. Without this capability, there is no way to refer to the column in subsequent SQL statements (like JOIN, UNION, or WHERE clauses).

      For full example, see the following forum post:

      http://forums.filemaker.com/posts/5cb40a5b1c

      Steps to reproduce the problem

      (See forum posting for helpful screenshots)

      Create a calculated field with an ExecuteSQL statement similar to the following:

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

      Expected result

      A list that shows the EmployeeID and the "EmployeeName" for any employee who is also listed as a manager.

      Note that "EmployeeName" is a virtual column, created by using the COALESCE function. (It cannot be referred to by using any of the names from the real columns that were used to create it).

      Actual result

      FileMaker Display = ?

      Viewing SQL error in Data Viewer shows the following error:

      The column named "EmployeeName" does not exist in table "PP1"

      Exact text of any error message(s) that appear

      "?"

      Configuration information

      I have not tested this, but I expect the same problem exists for any of the "CONDITIONAL" functions (p.32 of Filemaker 13's SQL reference manual), which would include CASE and NULLIF.

      These functions have the ability to create a "virtual" column, and therefore, the column should accept a NAME that can be used for other SQL operations (JOIN, UNION, WHERE, etc).

      Workaround

      None

      People_%28Table%29.png

        • 1. Re: ExecuteSQL, COALESCE, and "As" (Field Alias) - Does not work
          user20650

          Hi Doug,

          Try this:

          SELECT PP1.EmployeeID,
                         COALESCE ( PP1.LegalName, PP1.PreferredName) AS EmployeeName
          FROM People As PP1

          WHERE COALESCE ( PP1.LegalName, PP1.PreferredName) in (SELECT PP2.NameOfManager

                                                                                                                   FROM People as PP2)

           

          • 2. Re: ExecuteSQL, COALESCE, and "As" (Field Alias) - Does not work
            Doug Staubach

            Hi Arnoud:

            Thank you for your response - this is a useful workaround.

            After applying this method, I discovered by accident that the ORDER BY clause accepts non-named columns (by specifying the column sequence number), like so:

            SELECT PP1.EmployeeID
                  ,COALESCE ( PP1.LegalName, PP1.PreferredName)
            FROM People As PP1
            ORDER BY 2

            So, for the FileMaker support folks ... if we could refer to the COALESCE (or CASE) result by either column number, or Alias name -- we could use the resulting value for JOINS and filters. (We know a hidden number exists, because you already have it available in the "ORDER BY" function; we just need you to make it available for other SQL functions also).

             

            • 3. Re: ExecuteSQL, COALESCE, and "As" (Field Alias) - Does not work
              TSGal

              Doug Staubach:

              Thank you for your post.

              I recommend that you enter this suggestion into our Feature Requests web form at:

              http://www.filemaker.com/company/contact/feature_request.html

              The entries into this web form populate a database file that is hosted and monitored by our Product Management and Development departments.  Each entry is discussed and considered for possible implementation in a future release.  Although I can copy your post and paste it into the web form, there are a couple of questions asked on the web form that only you can answer.

              TSGal
              FileMaker, Inc.