AnsweredAssumed Answered

ExecuteSQL GROUP BY returning additional fields

Question asked by user5859 on Jul 3, 2012
Latest reply on Jul 5, 2012 by greglane

When using the executeSQL function with a GROUP BY I'm unable to return additional fields in Filemaker versions 11 and 12, as Filemaker has made the GROUP BY a lot stricter. Because Filemaker has made the GROUP BY syntax stricter there is no way to return additional fields unless you use a subquery in the FROM clause. An example of the work around taken from stack overflow below.

 

select e.empID, fname, lname, title, dept, projectIDCount
from
(
select empID, count(projectID) as projectIDCount
from employees E left join projects P on E.empID = P.projLeader
group by empID
) idList
inner join employees e on idList.empID = e.empID

 

After some unsuccessful experimentation it would seem that Filemaker's SQL does not support subqueries in the FROM clause.

http://forums.filemaker.com/posts/43d414725b - "The current SQL driver does not support subqueries in the FROM clause." 10/5/2012

 

GROUP BY worked flawlessly prior to version 11. (not including subquery)

 

Questions

========

1. Does this mean the SQL engine will be updated at a future time? We are already two Filemaker versions into a new SQL engine.

2. Is there another way to return additional fields when doing a GROUP BY without writing a lot of convoluted code?

Outcomes