ExecuteSQL() sums and group by ERROR...

[dwdata]

Hey gang,

Trying to figure something for a fellow lister. I am tring to run a query in FileMaker using ExecuteSQL() that works perfectly when I run a similar query in a normal SQL editor:

SELECT
p.pk_ProductID,
p.Description,
SUM(i.Quantity) AS sQTY  

FROM invoice_invoicelines_Product p  

JOIN invoice_InvoiceLines i ON p.pk_ProductID = i.fk_ProductID AND i.fk_InvoiceID IN (1,2,3)  

WHERE p.pk_ProductID IN (1,2,3)  

GROUP BY p.pk_ProductID

It produces a "?" (which is very informative).

I guess need to know if this is too much for FileMaker's SQL engine or if FileMaker is going to force me to use something that is not tradition SQL protocol/syntax.

 

Any feedback would be appreciated.

[Kris M]

? return indicates a syntax error. you already know that.

you might want to check out...

http://www.soliantconsulting.com/blog/2013/11/filemaker-query-builder

http://mightydata.com/tips-for-debugging-filemaker-sql-queries/

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

 

My guess would be to look at FMSQL implementations where In () is used. I suspect that the single quoted list is the problem.

http://filemakerhacks.com/2012/10/19/the-missing-fm-12-executesql-reference/

Edited October 23, 2015 by Kris M

[beverly]

error is determined if you put EvaluationError() around the query. Look up the standard FM errors.

But I see TWO columns (fields) in the SELECT and both must be in the GROUP BY (not just an FQL thing...). GROUP BY needs all non-aggregate columns listed, comma-delimited:

GROUP BY p.pk_ProductID, p.Description

Beverly

[dwdata]

Ah, that worked, Bev!

Strange - in mySQL it does not require the "p.Description" in the GROUP BY clause.

Thanks!