AnsweredAssumed Answered

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

Question asked by Doug Staubach on Sep 26, 2014
Latest reply on Oct 7, 2014 by TSGal


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


FileMaker Pro



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:

Steps to reproduce the problem

(See forum posting for helpful screenshots)

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

ExecuteSQL ( "
     ,COALESCE ( PP1.LegalName, PP1.PreferredName) AS EmployeeName
      People As PP1
      People As PP2
      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).