AnsweredAssumed Answered

Union Query Result set with ExecuteSQL can't be sorted correctly

Question asked by philmodjunk on May 24, 2012
Latest reply on May 29, 2012 by philmodjunk

Summary

Union Query Result set with ExecuteSQL can't be sorted correctly

Product

FileMaker Pro

Version

FMP 12

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:

ExecuteSQL ("
Select Name From Table1
UNION
Select Name From Table2
Order By 1 Desc
")

Expected result

Orange
Mary
John
Jim
Kiwi
Apple

Actual result

Jim
John
Mary
Orange
Kiwi
Apple

(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.)

Workaround

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.

Outcomes