Simple ExecuteSQL expression is wrong.

[doughemi]

This is just about as simple as an ExecuteSQL gets, but I can't figure out why it generates a "?".

ExecuteSQL ( 
"SELECT Count (\"voteYes\") FROM \"MotionVote\" WHERE \"motionYear\" = '2016' ";
 "" ; "" 
)

MotionVote is a TO; voteYes and motionYear are fields therein; voteYes can either be 1 or blank. The objective is to display a count of "yes" votes. What did I miss?

[Kevin Frank]

Answer #1: you don't need to wrap the year in single quotes.

Answer #2: you don't need to escape the names with double quotes (since they aren't reserved words, don't contain spaces or apostrophes, or begin with an underscore character), and you can use a dynamic parameter, which frees you up from having to worry about whether to use single quotes or not, e.g.,

ExecuteSQL ( 
"SELECT Count ( voteYes ) 
FROM MotionVote 
WHERE motionYear = ?" 
; "" ; "" ; 2016
)

Answer #3: for more info, check out this blog posting -- https://filemakerhacks.com/2012/05/02/fm-12-executesql-dynamic-parameters/

Edited July 20, 2016 by Kevin Frank

[Kevin Frank]

On reflection, having re-read your message, I would be inclined to construct the query like this:

ExecuteSQL ( 
"SELECT Count ( * ) 
FROM MotionVote 
WHERE motionYear = ? 
   AND voteYes = ?" 
; "" ; "" ; 2016 ; 1 
)

It makes more sense to me to constrain the results (via the WHERE clause), and then simply count them... but really that's just my preference.

[doughemi]

Thanks, Kevin. Would your second construct be a better generic template, then?

Edited July 20, 2016 by doughemi

[Kevin Frank]

You're welcome. Hope it helps.

[Kevin Frank]

Quote

Would your second construct be a better generic template, then?

I think so. It would never have occurred to me to approach it the way you did.

It makes more sense to me to do all the heavy lifting in the WHERE clause.

Also, for the sake of argument, if voteYes contained 0 and 1 rather than nothing or 1, my second version would return the correct answer but the first would not (since the zeros would be counted also).

[beverly]

Or

      SUM ( voteYes ) 

if the value is 1, 0 or empty, no need to search for voteYes = 1

beverly