ExecuteSQL ("SELECT COUNT (OrgID)
WHERE \"Date Ordered\" < $$P1End AND Status = 'Client' ";"";"")
Are variables not allowed in SQL queries or is my syntax incorrect?
Yes they are, and well, who knows – you may have a problem with the date format that the variable holds.
You can prevent this problem (and similar ones) by utilizing the “optional parameter” of ExecuteSQL(), e.g.
SELECT COUNT (OrgID)
WHERE \"Date Ordered\" < ? AND Status = ?
" ; "" ; "" ; $$P1End ; "Client"
where the arguments given in FileMaker syntax will automatically be formatted and/or quoted as necessary and injected in lieu of the placeholder '?'s.
You can use variable in executeSQL. You substitute le value with ? and append to the end of function the variable.
Look the example:
100 * Salary::Salary / ExecuteSQL("select sum(S.salary) from Employees E join Salary S on E.EmpID = S.EmpID where E.Department = ?"; ""; ""; Department)
Perfect. Thank you very much.
You've gotten your answer. I'll tell you why. The variable, when INSIDE the quoted query is considered literal text. FM does not substitute if the variable is text. However, using the ? Parameter allows the calc engine to place the VALUE of that variable before evaluating the query.
Another method is to not quote it:
WHERE x= '" & $myvar & "' ...
See how I had to be mindful of the single quote? That's why the preference to use the ? parameter instead. FMP takes care of single-quoting text and not quoting numbers.
-- sent from myPhone --
Thank you, Beverly.
It's so helpful to know ""why" something works.
If you use the quote then I suggest you use Evaluate() and EvaluationError() function
Thank you. I will try your suggestions.
If you use the Evaluate() around your query (processed before ExecuteSQL), it will work, but is unnecessary when the ? parameters are used:
I consider this INCORRECT:
_Q = Evaluate( " SELECT abc, def, ghi FROM $$tablVar WHERE abc = $$fieldVAR ")
or however it's being used.
I consider this CORRECT:
_Q = " SELECT abc, def, ghi FROM " & $$tablVar & " WHERE abc = " & $$fieldVAR
_Q = " SELECT abc, def, ghi FROM " & $$tablVar & " WHERE abc = ? "
Note that there are times when variables are used and the concatenation of the text (automatically evaluated, BTW) is used. This is useful to assign fields to variables so that the GetFieldName() will prevent your queries from breaking if the field (or table) are renamed. The other valuable place is for the IN() part of the WHERE clause.
The EvaluationError() is for debugging and I only use it if I get "?" result (snippet of Let() calc):
[ ... // assign vars
; _Q = ....
; _Result = ExecuteSQL ( _Q ; "" ; "" )
]; If ( _Result = "?" ; "ERROR: " & EvaluationError ( _Result ) ; _Result )
thanks for your suggestion. I wrote that if use ' (quote) or &(union) then use Evaluate() and EvaluateError(). In other case you use only ExecuteQuery().
Coincidentally, I noticed this excellent article by Kevin Franks related to the question I posted yesterday.
He mentions a Custom Function that I wish I had known about yesterday!