I'm using SQLE to generate queries for FMP12 Advanced. There are two issues I've run into. First, when I modify a query to include calculations via SELECT and GROUP BY criteria, the query performs correctly. When I choose Save and Finish, SQLE does not save those commands. This is the code before save (modified commands underlined):

SELECT a."Species", a."Mortality", Avg(a."Height_m")
FROM "Monitoring" a
WHERE a."Species" NOT LIKE ? AND a."Height_m" > ? AND a."Mortality" = ?
GROUP BY a."Species", a."Mortality"
ORDER BY a."Species" ASC

This is the code after "save and finish:"

SELECT a."Species", a."Mortality", a."Height_m"
FROM "Monitoring" a
WHERE a."Species" NOT LIKE ? AND a."Height_m" > ? AND a."Mortality" = ?
ORDER BY a."Species" ASC

Of course, I can copy the code to clipboard or as calculation and it retains the needed text, but it would obviously be more convenient to store queries properly in SQLE.

Second, some aggregate functions are supported but others return a query error (?). For example, in SELECT from the first code example, I have Avg(a."Height_m") to return the average of all height measurements by each group ("Species") measured. This works. Replacing "Avg" with "Count" or "Sum" works. Standard deviation ("Stdev" or "StDev") does not, and this is an important function for my queries. I've also tried it in ExecuteSQL directly in FMP, that did not work either. There are no zero, blank or one value groups in my data, so that shouldn't be the problem.

Ideas?

FMP Advanced 12.0v2
Mac OS X 10.7.5

Hi BGL,

Hope all is well. Right, I think we decided to have the queries revert to the Wizard entries, because if you started to go custom with aggregates, etc. then it would be hard to determine what to do if you went back to a Wizard step and made a change. The problem will be if you go back to one of the wizard steps, it will want to overwrite that part of the query. I'll take another look at this when I get a chance to see if this is something that would be easy to change.

bgl@mpg wrote:Second, some aggregate functions are supported but others return a query error (?). For example, in SELECT from the first code example, I have Avg(a."Height_m") to return the average of all height measurements by each group ("Species") measured. This works. Replacing "Avg" with "Count" or "Sum" works. Standard deviation ("Stdev" or "StDev") does not, and this is an important function for my queries. I've also tried it in ExecuteSQL directly in FMP, that did not work either. There are no zero, blank or one value groups in my data, so that shouldn't be the problem.

Right, the only real documentation on what's supported in FM's SQL is the ODBC guide, but that's not definitive as we've found some things that work that aren't referenced there, so it is pretty hits and miss and the ? is not that "illustrative." I would check out this custom function:

http://www.fmfunctions.com/fid/335

which allows you to get some more error information in the data viewer. Not sure how this guy figured that out, but I've found it helpful.

I'd also check out http://www.filemakerhacks.com. There's a great new article there by Beverly Voth on ExecuteSQL with a bunch of great stuff, and there's older great posts on ExecuteSQL by Kevin, who runs the site, that are great references.

hth!

Thank you Jason! I suspected that the revert was on purpose, and it makes sense why SQLE would do that. For now I'll save modifications in a text file, or copy them into FMP. Thanks also for the additional aggregate function info. Cheers

BGL