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

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

Outcomes