I know nothing about writing SQL scripts, but Filemaker can executesql
And this free app helps people write such scripts. (can't find link)
these links are about executesql
FM 12 ExecuteSQL, part 1 Apr 2012
The Missing FM 12 ExecuteSQL Reference October 19, 2012
You might get a free membership here.... https://fmdev.filemaker.com/login.jspa
Can you translate the SQL into a plain language description of what you have and and what you want? CAST is unfamiliar SQL to me.
The question prompted some searches, results may be useful.
Thank you David,
Very useful data, You have save me hours of internet searching.
The above mysql query looks trough all my records and divides everything by Year.
My records have an identifying field called Stat_number, the query grabs all my records for stat_Number "196",
the it divides the data by calendar year, columns for "2011" and columns for "2012" based on the entry date field called "sqldate"
by selecting only those records which are equal or greater than '2011-01-01' and equal or less than '2013-12-31'.
This way I can chart the data in one chart with a series for "2011" and another series for "2012"
I think that there's an error in the WHERE clause:
(sqldate >='2011-01-01' OR sqldate <'2013-12-31')
Matches to any valid date as any date will be either Greater than Jan 1 2011 OR less than Dec 31 2013. Perhaps you meant AND to get all records from Jan 1 2011 to Dec 31 2013?
To get data from different sets of related records in Columns is often called a cross tab report. Without using SQL, this is often done with portals placed side by side to produce columns of data. They are often one row portals with summary fields to show an aggregate value for a specific set of related/filtered records.