14 Replies Latest reply on Apr 17, 2016 6:27 PM by bigtom

    ExecuteSQL more than two AND operators

    bigtom

      I have run into something odd. Maybe this is a limitation of FM or I am missing something in the syntax. I and having an issue getting a result when using more than one AND operator (with the exception of BETWEEN ? AND ?). I have a table with a number of fields and I want to use ExecuteSQL to quickly get to a single value or list of values.

       

      Please assume the variables are set properly in a Let function.

       

      ExecuteSQL ( "SELECT \"UUIDpk\" FROM \"TableName\" WHERE \"DepartmentID\" = ? AND \"EmployeeID\" = ? AND \"EntryDate\" Between ? AND ?"; ""; ""; $deptID; $empID; $d1; $d2)

       

      If I remove any one of the AND expressions I get a proper resulting list, so there is no issue with the evaluation independently with any combination of two expressions.

       

      I can get the following things to work:

      WHERE exp1= ? AND exp2 = ?

      or

      WHERE exp1= ? AND "Date" BETWEEN ? AND ?

       

      I cannot get the following to work:

      WHERE exp1= ? AND exp2 = ? AND exp3 = ? AND exp4 = ? AND exp5 = ?

      or

      WHERE exp1= ? AND exp2 = ? AND exp3 = ? AND "Date" BETWEEN ? AND ?

       

      I have been able to work through through this in a few cases where I can ORDER the results but this does will not work all the time. Hopefully this is possible in some way. I realize FM is not SQL, but I did not see anything on a limit to two conditions only.

        • 1. Re: ExecuteSQL more than two AND operators
          beverly

          I try to use the parens "(" & ")" to make sure things evaluate the way I desire and to make the query clear:

           

          WHERE xxx

          AND ( ( yyyy OR zzz )

          OR ( bbb AND ccc ) )

           

          For your calcs, try:

          WHERE exp1= ?

          AND (exp2 = ?)

          AND (exp3 = ?)

          AND (exp4 = ?)

          AND (exp5 = ?)

           

          WHERE exp1= ?

          AND (exp2 = ?)

          AND (exp3 = ?)

          AND ("\Date\" BETWEEN ? AND ?)

           

          beverly

          • 2. Re: ExecuteSQL more than two AND operators
            bigtom

            I did try parens and did not get any different results.

            • 3. Re: ExecuteSQL more than two AND operators
              Mike_Mitchell

              bigtom wrote:

               

              If I remove any one of the AND expressions I get a proper resulting list, so there is no issue with the evaluation independently with any combination of two expressions.

               

              Might be a stupid question, but are there records in the table that match all the WHERE criteria? (Sometimes the really simple things come back and bite me.)

              • 4. Re: ExecuteSQL more than two AND operators

                FM does have a a SQL engine so this should work or there is a "bigger" issue (FM bug), which I doubt.

                 

                Could you post some data that doesn't work so we could see it and try it as well?

                 

                Thanks,

                 

                - m

                • 5. Re: ExecuteSQL more than two AND operators
                  bigtom

                  Mike,

                   

                  There are records present with the matching criteria. I tested with a few specific records.

                   

                  When I use any two of the criteria I get a result. So using departmentID & employeeID, departmentID & date, or employeeID & date all give result with the test record. Using all three gives a blank. No "?", just blank.

                  • 6. Re: ExecuteSQL more than two AND operators
                    beverly

                    BETWEEN can be unpredictable when it comes to FM, ExecuteSQL().

                    you might try

                    (\"Date\" >= ? AND \"Date\" <= ?)

                     

                    beverly

                    • 7. Re: ExecuteSQL more than two AND operators
                      karimhanafi

                      bigtom,

                       

                      I tested your eSQL formula with dummy data and it does work for me. It returns the ID of the 1 record (out of 4) matching the criteria.

                       

                      I don't think it has to do with FM SQL. Usually a blank output means that eSQL function did not find any record matching the criteria. Can you check that you have at least one record matching all the criteria? If yes, I would suspect something is going on with the local variables that are parameters to the function. Using script debugger and Data viewer tools, you should be able to check the vars just before the function computes.


                      I attached a screenshot of the result I am getting.


                      Hope that helps.


                      Karim

                      • 8. Re: ExecuteSQL more than two AND operators
                        bigtom

                        Clerical error with date format. One of the date variables was set to dd/mm/yyyy at one point in the script. The assumption that the variables were correct was wrong. It is always the small things.

                         

                        That date was selected by eSQL and was originally "yyyy-mm-dd". I tried using that eSQL result directly as a variable in the next eSQL to find the ID, but it did not like that even when I used GetAsText($result). Is there a way to use it directly to avoid converting yyyy-mm-dd to mm/dd/yyyy just to use it?

                        • 9. Re: ExecuteSQL more than two AND operators
                          Mike_Mitchell

                          I use this Custom Function (although someone else may have a better idea):

                           

                          Let ( [

                               inputDate = GetAsDate ( calendarDate ) ;

                               inputYear = Year ( inputDate ) ;

                               inputMonth = Right ( "0" & Month ( inputDate ) ; 2 ) ;

                               inputDay = Right ( "0" & Day ( inputDate ) ; 2 )

                          ] ;

                           

                               inputYear & "-" & inputMonth & "-" & inputDay

                           

                          )

                          • 10. Re: ExecuteSQL more than two AND operators
                            taylorsharpe

                            Dates can throw ExecuteSQL easily.  Can you confirm the "Entry Date" is a date field?  And the variables you put in the ExecuteSQL for date, $d1 and $d2... are you sure they are dates?  As in Date ( 4 ; 17 ; 2016 ) or confirm with GetAsDate function.  Just checking since I've seen that be a problem with dates and SQL. 

                            • 11. Re: ExecuteSQL more than two AND operators
                              taylorsharpe

                              If you want to return a Date with ExecuteSQL in whatever your current system format is, then you need to Coalesce the date:

                               

                              Let ( [

                                   F1 = "SELECT

                                                  COALESCE ( DateField,'' )

                                             FROM

                                                  SomeTable

                                             WHERE

                                                  Some Where Statement" ;

                                   F2 = ExecuteSQL ( F1 ; Char ( 9 ) ; ¶ ) ;

                                   F3 = GetAsDate ( F2 )   //  This makes sure FM treats this field as a date

                              ] ;  F3 )

                               

                              With Coalesce and my USA formatted OS, I get a date of 4/17/2016 instead of 2016-04-17.  However, note that if you ORDER BY a Coalesced field, it sorts it as if it is a text field which doesn't work for date sorts.

                              • 12. Re: ExecuteSQL more than two AND operators
                                bigtom

                                EntryDate is a date field, but when I use eSQL to get the first or last day from EntryDate of a set it is returned. as "yyyy-mm-dd".

                                 

                                What I am wondering at this point is if I can use this as the parameter instead of changing it back to a FM format. Maybe I need to format the variable as "DATE \"" & $sqlDateResult & "\"".

                                 

                                It is all working now that I found the small error but I am curious.

                                • 13. Re: ExecuteSQL more than two AND operators
                                  user19752

                                  The syntax use single quote, "DATE'" & $sqlDateResult & "'"

                                   

                                  Or, if you use the date result only for the parameter, embed SQL into WHERE clause. This worked.

                                  WHERE aDate BETWEEN (SELECT returnOnlyADate) AND (SELECT another)

                                   

                                  (I didn't test the slowness of BETWEEN now.)

                                  • 14. Re: ExecuteSQL more than two AND operators
                                    bigtom

                                    user19752 wrote:

                                     

                                    The syntax use single quote, "DATE'" & $sqlDateResult & "'"

                                     

                                    I will give this a try.