12 Replies Latest reply on Jun 13, 2017 2:04 PM by JamesPeragine

    ORDER BY date field using ExecuteSQL

    JamesPeragine

      I'm trying to sort primary keys in a list using ExecuteSQL and can't seem to get it to work for a date field.  It works fine if the sort field is a text field in FM.

       

      I know that SQL handles dates differently than Filemaker.  How can I Sort based on a column that's a date field?

       

      ExecuteSQL("SELECT L.\"_pkLeads\"

      FROM Leads L

      JOIN Merchants M

      ON   L.\"_fk_Merchants\" = M.\"_pkMerchants\"

      WHERE " & $$sqlFilter & "

      ORDER BY ? ";"";""; Leads::Date)

        • 1. Re: ORDER BY date field using ExecuteSQL
          philmodjunk

          Internally, dates are really number field. That internal value doesn't change just because you refer to it in an SQL expression.

           

          But you are using a parameter value for your date. That uses the same value for every record and thus sort doesn't work.

          1 of 1 people found this helpful
          • 2. Re: ORDER BY date field using ExecuteSQL
            beverly

            ORDER BY ?

            Is supplying the value of that field, not the name of that field.

             

            Sent from miPhone

            1 of 1 people found this helpful
            • 3. Re: ORDER BY date field using ExecuteSQL
              user19752

              As phil wrote, using parameter makes no sense. Why don't you use the field in SQL?

              ORDER BY L.\"Date\"" ; "" ; "" )

              1 of 1 people found this helpful
              • 4. Re: ORDER BY date field using ExecuteSQL
                JamesPeragine

                Thanks for the input guys.

                 

                But you are using a parameter value for your date. That uses the same value for every record and thus sort doesn't work.

                 

                Is supplying the value of that field, not the name of that field.

                 

                Thanks guys good to know.

                 

                I tried using the field in the SQL statement but it returns ?

                I can use the other non-date fields and they work as expected.

                • 5. Re: ORDER BY date field using ExecuteSQL
                  coherentkris

                  You said...

                  "I tried using the field in the SQL statement but it returns ?"

                   

                  Thats because DATE is a reserved word

                  Reserved words in FileMaker Pro | FileMaker

                  To use a field named date in a SELECT you can escape quote it \"date\" or change the field name to something other than date

                  1 of 1 people found this helpful
                  • 6. Re: ORDER BY date field using ExecuteSQL
                    JamesPeragine
                    Thats because DATE is a reserved word

                     

                    Ahhhhhhh  thank you coherentkris     Working perfectly now.   I suppose it's good practice to escape all the fields in an SQL Statement.  Thanks guys that was driving me nuts!

                    • 7. Re: ORDER BY date field using ExecuteSQL
                      philmodjunk

                      It's good practice to quote all field and table occurrence names, yes. It's also good practice to write the query in such a way that none of these names are "hard coded" into the expression but referenced in such a way that future name changes to a field or table occurrence do not cause the query to fail and return a ? syntax error.

                       

                      There are multiple approaches that people use. The method I use is presented as an example in the "button bars as value lists" section of:

                       

                      Adventures in FileMaking #2-enhanced value selection

                       

                      What I have set up not only expands the expression into one that is "flexible", but includes a tool that eliminates a lot of the tedium involved in writing queries in such more complex format.

                      1 of 1 people found this helpful
                      • 8. Re: ORDER BY date field using ExecuteSQL
                        JamesPeragine

                        Thanks Phil this looks awesome.

                        • 9. Re: ORDER BY date field using ExecuteSQL
                          JamesPeragine

                          Okay one last question on this.  It's working perfectly now as part of the SQL Statement. 

                          I'd like to set this up to be used in many different scenarios by passing the sort field in a script parameter.

                           

                          I've tried both

                           

                          ExecuteSQL("SELECT L.\"_pkLeads\"

                          FROM Leads L

                          JOIN Merchants M

                          ON   L.\"_fk_Merchants\" = M.\"_pkMerchants\"

                          WHERE " & $$sqlFilter & "

                          ORDER BY \"?\" ";"";""; Get (ScriptParameter) )

                           

                          with the ScriptParameter as "\"Date\""

                           

                          and

                           

                          ExecuteSQL("SELECT L.\"_pkLeads\"

                          FROM Leads L

                          JOIN Merchants M

                          ON   L.\"_fk_Merchants\" = M.\"_pkMerchants\"

                          WHERE " & $$sqlFilter & "

                          ORDER BY \"Get (ScriptParameter)\" ";"";"" )

                           

                          with the ScriptParameter as "Date" and also tried "\"Date\""

                           

                           

                          It's not sorting properly with either method.

                          • 10. Re: ORDER BY date field using ExecuteSQL
                            coherentkris

                            The SELECT statement is a continuous string...To insert a derived value into a string you use "first part of string " & derived_value & "the rest of the string"

                            so....

                            ExecuteSQL("SELECT L.\"_pkLeads\"

                            FROM Leads L

                            JOIN Merchants M

                            ON   L.\"_fk_Merchants\" = M.\"_pkMerchants\"

                            WHERE " & $$sqlFilter & "

                            ORDER BY " & Get (ScriptParameter) & "\";"";"" )

                             

                            You did the right thing for the global variable call but not the script parameter.

                             

                            You should get used to using let () for this.

                             

                            Let ( [

                            _sp = Get ( ScriptParamter);

                            _filter = $$sqlFilter

                            _sql = "SELECT L.\"_pkLeads\"

                            FROM Leads L

                            JOIN Merchants M

                            ON   L.\"_fk_Merchants\" = M.\"_pkMerchants\"

                            WHERE " & _filter & "

                            ORDER BY " & _sp & "\"

                            ];

                            ExecuteSQL ( _sql ; "" ; "" )

                            )

                            1 of 1 people found this helpful
                            • 11. Re: ORDER BY date field using ExecuteSQL
                              JamesPeragine

                              coherentkris

                              Thank you so much for pointing that out.   I couldn't get the syntax right at the end.

                               

                              Here's what I got working. 

                               

                              Let (

                              SP = Get (ScriptParameter) ;

                               

                              ExecuteSQL("SELECT L.\"_pkLeads\"

                              FROM Leads L

                              JOIN Merchants M

                              ON L.\"_fk_Merchants\" = M.\"_pkMerchants\"

                              WHERE " & $$sqlFilter & "

                              ORDER BY" & SP ;"";"")

                              )

                               

                              Script Parameter -   "\"Date\""

                               

                               

                               

                              I've tried to assemble the rest of the SQL statement in the Let arguments but can't seem to get it working.

                               

                               

                               

                               

                               

                               

                              • 12. Re: ORDER BY date field using ExecuteSQL
                                JamesPeragine

                                Got the Let statement right now.  I had to break apart the statement builders a bit further.

                                 

                                 

                                Let ( [

                                _sp = Get (ScriptParameter);

                                _filter = $$sqlFilter;

                                _sql1 = "SELECT L.\"_pkLeads\" FROM Leads L JOIN Merchants M ON L.\"_fk_Merchants\" = M.\"_pkMerchants\" WHERE ";

                                _order = " ORDER BY "

                                 

                                 

                                ];

                                 

                                 

                                ExecuteSQL ( _sql1 & _filter & _order & _sp; "" ; "" )

                                 

                                 

                                 

                                 

                                )