I'd be interested in hearing from the FileMaker Techs whether FileMaker's ExecuteSQL function suports nesting one Select statement inside the other like this.
If you remove that part of the expression and subsitute a constant for testing purposes, do you get a result that is correct for that constant value?
If so, using ExecuteSQL, you may need to use this expression:
ExecuteSQL ( "
SELECT oli.ID_Product, sum( oli.qty )
FROM material oli
WHERE oli.ID_Order = 'SQ-000568-01'
GROUP BY oli.ID_Product
HAVING sum( oli.qty ) > ?
"; ; ; ExecuteSQL ( "SELECT sum( ap2.Quantity) FROM Artikelposten ap2 WHERE ap2.id = oli.ID_Product" )
It's worth a try any way...
Thanks for looking at this :)
Replacing with constant
If I replace oli.ID_Product with 'APS-KIT0' and play around with the quanttiy, I always get correct results. So it seems, that the correlation part is broken/not avaiable.
Using your suggestion for execute():
This doesn't work because we get two seperate SQL queries. The second shorter query does not know what "oli.ID_Product" is. Meaning, there is no correlation.
When it comes to nested queries, how should I know which ones are supported? I consulted the odcb/jdcb guide and functions reference. They do not say that you can have a nested query in the WHERE statement, but it works :) Do you know any other sources ??
Anyway..... when it come to the new sql function, it would be a blast if it could perform such basic tasks. Live would be come extremly easy :)
Sooo, have you informed the technicians? Should I send a request?
I consulted the odcb/jdcb guide and functions reference. They do not say that you can have a nested query in the WHERE statement, but it works :) Do you know any other sources ??
I do not. I have previously indicated to the folks at FileMaker Inc. that they need more complete documentation for this new function and what can or cannot be done with it.
They also need to add a wizard for building and testing SQL queries.
A wizard for building sql queries? I'd say there are pleny of docs on the net to learn from....well. But a sql reference for FM would be nice indeed :)
I requested correlated sql queries as a featrure. I hope they realize that. That'd be fantastic :)
there are pleny of docs on the net to learn from
Of course they are, but every SQL engine that I've ever worked with comes with it's own "flavor" of SQL. Stuff that works in one doesn't work in the other without modification.
And for me, there's no substitute for being able to build a trial, expression with actual tables and then be able to inspect a table view of the results while being able to iterate back and forth until it's all just right. Very much like testing out a complex expression in FileMaker Advanced's Data Viewer.
The Data Viewer is actualy useful for testing ExecuteSQL, but you get that frustrating ? so easy and a similar widgit but with tools for setting up the joins with correct syntax would save a lot of time.
Most SQL DB's have such a tool for precisely that reason. SeedCodes SQL Explorer is a good start, but I want more capabilities than it has coupled with better documentation.