Union Query Result set with ExecuteSQL can't be sorted correctly
Operating system version
Windows XP, SP3
Description of the issue
Been reviewing and testing different SQL expressions using the new in FMP 12 ExecuteSQL function.
The following SQL produces a list of values merged from two different tables, but fails to sort them as a whole. The Order By clause only applies to the result set from the table immediately above it. Adding second Order By clause before the UNION term is incorrect syntax--producing a ? error indicator and wouldn't produce the desired results even if it did work.
Admittedly, my SQL is very rusty and this is compounded by the fact that I can find almost no documentation on what syntax can or cannot be used in the SQL for this function other than that data and schema modifying queries cannot be executed.
For example, most SQL engines have a way to encode a field or table name so that it is not mistaken for a reserved word but I can't by trial and error figure out what if anything works for this function. (Had to change a field name from Date to dDate before the query worked in another test of mine...)
Steps to reproduce the problem
Define two tables, Table1 and Table2 with the text field Name defined in each.
Put names: Jim; John; Mary into table 1. Put Apple; Orange; Kiwi into table 2.
Define an unstored calculation field with:
Select Name From Table1
Select Name From Table2
Order By 1 Desc
(Only the names from Table2 are sorted by the Order By clause and they remain in the list following the names returned from the first table.)
Only work around so far would be to use a script or custom function to re-order the names after the query produces the combined list of values.