ExecuteSQL, COALESCE, and "As" (Field Alias) - Does not work
Operating system version
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
" ; "" ; "" )
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).
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
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).