Jump to content

ExecuteSQL() sums and group by ERROR...


This topic is 3052 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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.

Link to comment
Share on other sites

? 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 by Kris M
Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 3052 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.