11 Replies Latest reply on Jul 13, 2017 2:49 PM by user19752

    Set as DATE Type EXECUTVESQL

    JamesPeragine

      I'm using ExecuteSQL()  to Compare two dates in Join.  How can I pass the field type as DATE()

       

       

      ExecuteSQL ( "SELECT Count (*) FROM \"KabbageSubmissions_Filter\" S JOIN Kabbage777 K ON S.\"_pkKabbageSubmissions\" = K.\"_fkKabbageSubmissions\" 

      WHERE \"_pkSubmissions\" = K.\"_fkSubmissions\"

      AND

       

      S.\"Channel Add Date\" = K.\"ChannelAddDate\"

      AND

      S.\"Credit Consent Date\" = K.\"CreditConsentDate\" "

       

      ; "" ; "")

       

      The results aren't correct and I think it's because SQL isn't seeing both fields as dates.

        • 1. Re: Set as DATE Type EXECUTVESQL
          philmodjunk

          If both fields are defined as dates, it should not be a problem.

          • 2. Re: Set as DATE Type EXECUTVESQL
            user19752

            And I can't get what do you try to count, doing all comparing same fields equals, one pk=fk is used in JOINing but another is in WHERE...

            • 3. Re: Set as DATE Type EXECUTVESQL
              Orlando

              Looking at the query, are you missing an "S." after the WHERE clause? might be why its not returning anything potentially.

               

              -- Orlando

              • 4. Re: Set as DATE Type EXECUTVESQL
                yamu

                Often, I have the same problem when comparing a Date with a Timestamp field. In such cases I do it this way:

                 

                ......

                CAST(S.\"Channel Add Date\" AS DATE) = CAST(K.\"ChannelAddDate\" AS DATE)

                AND

                CAST(S.\"Credit Consent Date\" AS DATE) = CAST(K.\"CreditConsentDate\" AS DATE)

                .....

                 

                With the Sql CAST Operator you can convert datatypes. See also the Filemaker SQL Reference Manual.

                 

                Hans

                • 5. Re: Set as DATE Type EXECUTVESQL
                  beverly

                  I agree. This query does not seem correct.

                   

                  Sent from miPhone

                  • 6. Re: Set as DATE Type EXECUTVESQL
                    beverly

                    Are you trying to base this off a relationship in the graph?

                    Post your RG, if possible. The Query should be context agnostic (not using the relationships).

                     

                    If there is an error ("?" result), what is the error code? Use EvaluationError() around the ExecuteSQL.

                    Beverly

                    • 7. Re: Set as DATE Type EXECUTVESQL
                      JamesPeragine

                      Thanks for the feedback guys.  I did indeed post an an incorrect version of the calculation, my apologies it was late and my brain was fried.  Here it is corrected (and with Yamu's help)

                       

                      ExecuteSQL ( "SELECT Count (*) FROM \"KabbageSubmissions_Filter\" S JOIN Kabbage777 K ON S.\"_pkKabbageSubmissions\" = K.\"_fkKabbageSubmissions\"

                      WHERE

                      CAST(S.\"Channel Add Date\" AS DATE) = CAST(K.\"ChannelAddDate\" AS DATE)

                      AND

                      CAST(S.\"Credit Consent Date\" AS DATE) = CAST(K.\"CreditConsentDate\" AS DATE) "

                       

                      ; "" ; "")

                       

                      Thanks Yamu that is what I was looking for and couldn't remember how to Cast the field AS DATE in SQL.   The results are still coming back incorrect.   The SQL statement is returning 146 records wheres Filemaker is returning 1342 (which is the correct number)   My suspicion is that NULL values are the culprit here.  In Filemaker empty fields equal empty fields but (as far as I know) empty fields are handled differently in SQL.  I'll take a crack and fixing it and posting the solution if I can.

                       

                      Any suggestions on best practices in dealing with NULL values would be greatly appreciated.

                      • 8. Re: Set as DATE Type EXECUTVESQL
                        philmodjunk

                        You shouldn't have to cast them as dates unless they are not defined as dates--such as needing to use time stamps as dates.

                         

                        IS Null

                         

                        Is the clause that detects null values in a field so you might do something with OR FieldnameHere IS Null

                         

                        as part of your where clause.

                        • 9. Re: Set as DATE Type EXECUTVESQL
                          JamesPeragine

                          Thanks Phil

                           

                          Here's what I have now and It's counting correctly

                           

                          ExecuteSQL ( "SELECT Count (*) FROM \"KabbageSubmissions_Filter\" S JOIN Kabbage777 K ON S.\"_pkKabbageSubmissions\" = K.\"_fkKabbageSubmissions\"

                          WHERE

                          CAST(S.\"Channel Add Date\" AS DATE) = CAST(K.\"ChannelAddDate\" AS DATE) OR CAST(S.\"Channel Add Date\" AS DATE) IS NULL

                          AND

                          CAST(S.\"Credit Consent Date\" AS DATE) = CAST(K.\"CreditConsentDate\" AS DATE)  OR CAST(S.\"Credit Consent Date\" AS DATE) IS NULL

                          "

                          ; "" ; "")

                           

                          If I want to separate these two WHERE clauses so that they are treated as separate clauses would I surround them in parenthesis? ie. 

                           

                          ExecuteSQL ( "SELECT Count (*) FROM \"KabbageSubmissions_Filter\" S JOIN Kabbage777 K ON S.\"_pkKabbageSubmissions\" = K.\"_fkKabbageSubmissions\"

                          WHERE

                          (CAST(S.\"Channel Add Date\" AS DATE) = CAST(K.\"ChannelAddDate\" AS DATE) OR CAST(S.\"Channel Add Date\" AS DATE) IS NULL)

                          AND

                          (CAST(S.\"Credit Consent Date\" AS DATE) = CAST(K.\"CreditConsentDate\" AS DATE)  OR CAST(S.\"Credit Consent Date\" AS DATE) IS NULL)

                          "

                          ; "" ; "")

                          • 10. Re: Set as DATE Type EXECUTVESQL
                            philmodjunk

                            Parenthesis will control order of evaluation just as they do in any typical math expression or programming language.

                             

                            If you group them like that, The "or" portion is evaluated first and you get "True" only if both parenthetical expressions are True.

                             

                            I would have thought that you'd write it this way though:

                             

                            ( FieldA = FieldB) or

                             

                            (Field A IS Null AND Field B Is Null )

                             

                            That replicates the way that two null fields evaluate as equal null in a regular (Non SQL) FileMaker expression.

                            • 11. Re: Set as DATE Type EXECUTVESQL
                              user19752

                              If empty value was a trouble, it is worth to try without CAST. As phil mentioned it shouldn't be needed for both date field, and CAST would make searching slower.