4 Replies Latest reply on May 29, 2012 2:12 PM by philmodjunk

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

    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.