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.