AnsweredAssumed Answered

ExecuteSQL more than two AND operators

Question asked by bigtom on Apr 17, 2016
Latest reply on Apr 17, 2016 by 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 = ?


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


I cannot get the following to work:

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


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.