ExecuteSQL, Coalesce and "As" (Alias) - Not Working
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?
Note - Sample of People (Table) is attached as an image below.
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