AnsweredAssumed Answered

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

Question asked by Doug Staubach on Sep 26, 2014
Latest reply on Sep 27, 2014 by rwu2359

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

Outcomes