4 Replies Latest reply on Oct 9, 2015 9:24 AM by WilliamSpangler

    FileMaker SQL Engine and Calculation Engine compare differently

    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")

        • 1. Re: FileMaker SQL Engine and Calculation Engine compare differently
          hschlossberg

          What you are finding seems to me to be correct behavior since case is relevant in SQL.  I haven't tried, but have you tried with either:

           

          ORDER BY field COLLATE NOCASE

          or

          ORDER BY LOWER(field), field

          should work to be sure that the uppercase ones still end up ahead of the lowercase ones

          • 2. Re: FileMaker SQL Engine and Calculation Engine compare differently
            WilliamSpangler

            None of these work

            -------------------

            ExecuteSQL(" SELECT team FROM schedule ORDER BY team COLLATE NOCASE ";"";"") => ?

            ExecuteSQL(" SELECT team FROM schedule GROUP BY LOWER(team)";"";"") => ?

             

            Does work, but not what i'm looking for

            ---------------------------------------

            ExecuteSQL(" SELECT team FROM schedule ORDER BY LOWER(team), team ";"";"")

             

            What i'm trying to do:

            ----------------------

            Given this table:

            ExecuteSQL(" SELECT start_dt, team FROM schedule ";"";"")

            2015-11-01,Med E

            2015-11-03,MOCC

            2015-11-10,Nocturnist

            2015-11-05,Med E

            2015-11-07,Mocc

            2015-11-14,Nocturnist

            2015-10-15,Consult

            2015-10-25,Consult

             

            Built a filtered list of unique sorted keys:

            If the keys are not unique and sorted properly, then list_binary_search will think a value is in the wrong place and return 0 for not found.

            So I have to create a calculation field of Lower(field) and query the SQL on that.

            Set Variable $teams_new = ExecuteSQL(" SELECT team_lower FROM schedule WHERE start_dt >= ? GROUP BY team_lower ";"";"";"11/1/2015")

            "med e¶mocc¶nocturnist"

             

            Then given a column imported from a spreadsheet:

            Set Variable $teams_imported = "mocc¶med e¶mocc¶nocturnist¶icu";

             

            Find the indexes to the keys:

            Set Variable $teams_new_match = map_list_binary_search($teams_new; $teams_imported)

            "2¶1¶23¶0"

             

            Convert to boolean list matching 0:

            Set Variable $teams_new_match = map_compare("$teams_new_match; "0"; "=")

            "0¶0¶00¶1"

             

            Boolean NOT the list

            Set Variable $teams_new_match = map_compare("$teams_new_match; ""; "not")

            "1¶1¶1¶1¶0"

             

            Sum of list

            Set Variable $teams_new_match = list_sum($teams_new_match)

            4

             

            Ratio of matches / total rows for the spreadsheet column

            Set Variable $match_ratio = $teams_new_match / ValueCount($teams_imported)

            .80

             

            Functions used:

            ----------------------

            Custom Function list_binary_search(a, val_search)

            Let([i_max = PatternCount(a; ¶)];

            If(i_max = 0; // single element list

              If(val_search = a;

               1; // found in single element list

               0 // not found

              );

              Let([i_mid = Round(i_max/2; 0); val_mid = GetValue(a; i_mid)]; // mid point and value at mid point

               If(val_search = val_mid; // checking mid value otherwise every search would traverse all levels

                i_mid; // found in middle of list

                If(val_search < val_mid;

                 list_binary_search(Left(a; Position(a; ¶; 1; i_mid - 1) - 1); val_search); // recurse through left of list

                 Let([result = list_binary_search(Right(a; Length(a) - Position(a; ¶; 1; i_mid)); val_search)];

                  If(result = 0; 0; i_mid + result) // recurse though right of list

                 )

                ) // search_for < val_mid

               ) // search_for = val_mid

              ) // Let i_mid, val_mid

            ) // multiple element list

            ) // Let i_max

             

            Custom Function map_list_binary_search(a, b)

            If(PatternCount(b; ¶) = 0;

            list_binary_search(a; b); // search a for single element b

            list_binary_search(a; GetValue(b; 1)) & ¶ &

              map_list_binary_search(a; Right(b; Length(b) - Position(b; ¶; 1; 1))) // return first element search with recursion rest

            )

             

            Custom Function compare(a, b, op)

            If(op = "=";

            a = b;

            If(op = "<";

              a < b;

              If(op = ">";

               a > b;

               If(op = "and";

                a and b;

                If(op = "or";

                 a or b;

                 If(op = "not";

                  not a;

                  If(op = "xor";

                   a xor b;

                   ""

                  )

                 )

                )

               )

              )

            )

            )

             

            Custom Function map_compare(a, b, op)

            If(PatternCount(a; ¶) = 0;

            compare(a; b; op); // compare the only element in a

            // compare first element of a and recurse with rest of elements

            compare(GetValue(a; 1); b; op) & ¶ &

              map_compare(Right(a; Length(a) - Position(a; ¶; 1; 1)); b; op)

            )

             

            Custom Function list_sum(a)

            If(PatternCount(a; ¶) = 0;

            a; // return a

            // return first element of a plus recursion call

            GetValue(a; 1) +

              list_sum(Right(a; Length(a) - Position(a; ¶; 1; 1)))

            )

            • 3. Re: FileMaker SQL Engine and Calculation Engine compare differently
              user19752

              This case using DISTINCT instead of GROUP BY can run on FM.

               

              ExecuteSQL(" SELECT DISTINCT LOWER(team) FROM schedule ORDER BY 1";"";"")

               

              But, making calculation field is good manner for performance. (But twice, in FM not sure it is really good...)

               

               

              You can get the last result without custom functions, without sorting the list.

              ValueCount ( FilterValues ( $teams_imported ; $teams_new ) ) / ValueCount ( $teams_imported )