WilliamSpangler

FileMaker SQL Engine and Calculation Engine compare differently

Discussion created by WilliamSpangler on Oct 7, 2015
Latest reply on Oct 9, 2015 by WilliamSpangler

This causes problems when using the output of a mixed case sorted SQL query in a function or script (e.g. partitioning a sorted list or looping through one sorted list inside the loop of another sorted list).

 

FileMaker Calculation Engine (a is less than B):

"a" < "B" returns 1

 

FileMaker Layout (a is less than B):

find >a returns "B"

find <B returns "a"

sorted returns "a", "B"

 

FileMaker SQL Engine (a is greater than B)

ExecuteSQL( " SELECT CASE WHEN 'a' < 'B' THEN 1 ELSE 0 END FROM test ";"";"") returns 0

ExecuteSQL( " SELECT col FROM test WHERE 'B' < col ";"";"") returns "a"

ExecuteSQL( " SELECT col FROM test WHERE 'a' > col ";"";"") returns "B"

ExecuteSQL( " SELECT col FROM test ORDER BY col ";"";"") returns "B", "a"

 

The only workarounds are:

- Creating calculated fields of Lower(field) for querying with SQL

- Creating a custom functions of "less than" and "greater than" that operate the same as the SQL engine

- Implementing quicksort in custom functions (slows debug mode)

 

FileMaker SQL cannot GROUP BY LOWER(field), otherwise the list can be lowercase and sorted in one step.

Lower(list) won't work because it won't be sorted: ("B,a") will be ("b,a")

Outcomes