I have not been able to find a reference guide to Filemaker12's implementation of SQL commands in their ExecuteSQL() function.
Is there such a guide that, for example, lists what SQL commands and sytax they support?
Start with the help on the function: <http://www.filemaker.com/12help/html/func_ref3.33.6.html>
You may only use the SELECT statement (including UNION) with this function.
Use the FROM clause to specify the table(s), including the JOIN ("relationship matches")
Use the WHERE clause to specify the "find criteria"
Use the ORDER BY clause to specify the "sort criteria"
See the "FileMaker 12 ODBC and JDBC Guide" for full information on the SELECT (chapter 7, starting on page 34 "SELECT statement").
(direct link to the PDF <http://www.filemaker.com/support/product/docs/12/fmp/fm12_odbc_jdbc_guide_en.pdf>
- also available from your help menu)
I have not tried the "FOR UPDATE clause" with this function.
Have you been able to use the "Group By" function?
Greg, GROUP BY is one of those things you need to study. If you use aggregate function (COUNT, SUM, etc.) the the fields need not be named in the GROUP BY clause. However, other fields/columns may all need to be listed.
SELECT * COUNT( state )
GROUP BY city
This may fail as other "contacts" columns are not in the GROUP BY.
There are several online resources, so search for "SQL order by", to see the 'rules'.
-- sent from my iPhone4 --
Oops! Search for "SQL GROUP BY" not order by...
I'll check it out. From what I have read todate the FM ExecuteSQL is Text only so I'll see how that works.
Correct. What the function *returns* is text, but if you use the aggregates, you could parse the results if needed. Remember that you control the delimiters.
I'll post tithe results...
SELECT b."restaurant_name", sum (a."cnt_ex_taste")
FROM "ccc" a
INNER JOIN "restaurant" b ON a."kf_restaurant_id" = b."kp_restaurant_id"
Group By b."restaurant_name"
b.restaurant_name sum (a.cnt_ex_taste)
Centre Point 2
Red Hills Road 2
Twin Gates 7
This is very COOL and leads to some crazy possibilities.
figuring that others may search for resources here...
You might not find a reference guide, but you will find some good resources:
Greg Lane (Skeleton Key) webcast
Kevin Frank's FileMaker Hacks articles on ExecuteSQL including his DevCon 2012 ExecuteSQL "unconference" session.
Example Custom Functions, here and here, from Andrew Duncan (Databuzz)
Brian Schick (Beezwax) blogpost on designing and formatting ExecuteSQL queries.
Excute SQL can only read data of course, and otherwise has the same vocabulary limutations as FM ODBC support, as it uses the same engine under the hood.
Retrieving data ...