The Missing FM 12 ExecuteSQL Reference

There seem to be many questions about the usage of SQL (Structured Query Language) with the ExecuteSQL function in FileMaker 12. This tutorial attempts to explain some of the SQL terms, if you are new to writing SQL statements. Since there are already many examples of how to write the ExecuteSQL queries, links to these will be listed at the end of this article. If you don’t need to learn the terms, jump right to the Helpful Example Databases section, below. There you will find links to solutions that help you create and test your queries.

This is not a complete SQL guide, as other databases may use other syntax. This is not a complete FileMaker and SQL guide, as FileMaker may be an ODBC source and the SQL queries made against it may vary from the terms used by ExecuteSQL(). This is not a complete FileMaker and ESS guide using SQL calls (if using Import or Execute SQL script steps or ExecuteSQL() function or ESS). It may not have all the nuances needed for other data sources. This is the ExecuteSQL() function reference for which you’ve been waiting. The FileMaker 12 ODBC and JDBC Guide is helpful, but it has uses outside (and beyond) the ExecuteSQL() function. Any discrepancies between the reference and the function will be noted here, if possible.

To become familiar with the function, start with the ExecuteSQL, FM12 help topic.

The “?” arguments are used with the ExecuteSQL(sqlQuery; fieldSeparator; rowSeparator {;arguments…}) function to pass parameters to the query. The “?” can be used in any part of the SELECT statement, although typically is used to pass search criteria in the WHERE clause.

FMP12 ExecuteSQL() is for SELECT Only

At this time, using the FileMaker 12 ExecuteSQL function, the SQL statement SELECT is a way to return delimited results to a field or variable (with these optional features):

  • find (with or without comparison criteria)
  • use constants and literals (as results and as comparison criteria)
  • concatenate, calculate & summarize data (for results)
  • sort (by multiple fields/columns of results)
  • join (create temporary relationships, including self-joins for results)
  • union (stack or concatenate SELECTs from several tables and show in results)
  • group results

The xDBC Guide, Page 37, Supported Standards (with additional notes). These terms are defined in this article:

SELECT [DISTINCT] {* | column_expression [[AS] column_alias],...}
    use fields, constants, calculations and functions
FROM table_name [table_alias], ...
    [ JOIN table_name ON matches ]
    list of tables or explicit relationships
[ WHERE expr1 rel_operator expr2 ]
    comparisons with AND & OR
    comparisons using LIKE, IN, or BETWEEN..AND
    can contain nested SELECT for IN
[ GROUP BY {column_expression, ...} ]
    list all fields NOT in an aggregate function
[ HAVING expr1 rel_operator expr2 ]
    comparisons using aggregate functions
[ UNION [ALL] (SELECT...) ]
    each SELECT must return the same # of columns
[ ORDER BY {sort_expression [DESC | ASC]}, ... ]
    comma-separated sort list

Find Everything in a Table

In its most basic statement, SQL SELECT will find all columns (fields) from a single table:

SELECT * FROM mytable

You must have something to find (SELECT *) and a table (FROM «mytable»). You must use FileMaker table occurrences (T.O.) as named on the relationship graph for your ExecuteSQL queries, but they will evaluate as if the base table had been selected. The function will not “filter” from the relationship (or any) context. The table occurrence must be ON the relationship graph in the file where the ExecuteSQL is performed, as you cannot query an unknown datasource. And the table must have at least one record to return any result (see the system functions in the example database).

NOTE: there is no differentiation between FileMaker table occurrences and External SQL Sources (ESS) table occurrences on the Relationship graph. Some of the SQL functions may or may not work as expected.

No FileMaker layouts or relationships are required to make ExecuteSQL return results. Keep in mind that if you are using fields to supply the criteria (any clause), that you may need to be on a layout that shows the field available in the Specify Calculation dialog. The “found sets of records” are not used when using the ExecuteSQL function. All records are available (with permission) to make the queries.

The “*” (asterisk) is a shortcut for “all fields/columns” and since we have not used a WHERE clause, all records and all fields will be returned from the specified table occurrence.

ExecuteSQL ( " SELECT * FROM mytable "
    ; "" ; "" ) // use the default delimiters

If you have named your table occurrence with characters (spaces or other characters that may return an error to your result), then quote it:

ExecuteSQL ( " SELECT * FROM "my table" " 
    ; "" ; "" )

Names are case insensitive for SQL tables and columns. “My Table” is the same as “my table”. The SQL statement commands are also not case sensitive, so that ” SELECT * FROM ” is the same as ” select * from “. My preference is to use uppercase for the keywords, so they are easy to find in my scripts and calculations.

Find Specific Fields in a Table

Add the names of fields to return just those results. Since no layout is used, any fields in the table occurrence can be used. Quote the field names if they contain spaces or other characters that might return an error. Field names that use reserved words must be quoted, too:

ExecuteSQL ( " SELECT FirstName, LastName, State, Zipcode 
    FROM myContacts " 
    ; "" ; "" )

ExecuteSQL ( " SELECT "First Name", "Last Name", 
    State, Zipcode 
    FROM myContacts " 
    ; "" ; "" ) // spaces in field names

ExecuteSQL ( " SELECT "date", amount
    FROM sales
    WHERE "date" >= '2012-01-01'
        AND amount > 500 "
    ; "" ; "" ) // date is a RESERVED WORD

ORDER BY = Sorting with ExecuteSQL!

What a nice list of contacts we have. Can we sort them? Yes, the SQL clause ORDER BY is our sorting mechanism. It uses a comma-delimited list of fields/columns to sort. You can optionally specify DESC (descending sort order). The ASC (ascending sort order) is the default so it is un-necessary to specify. Remember that ORDER BY is always the last clause in your SQL statement.

The following will sort the last name field in a reverse order and sort the first name field (ASC by default). In the FileMaker Sort dialog, you make the same kind of sorting order.

ExecuteSQL ( " SELECT FirstName, LastName, State, Zip
    FROM myContacts
    ORDER BY LastName DESC, FirstName " 
    ; "" ; "" )

sort_dialo

Concatenation & Calculations in the SELECT Statement

It’s often convenient to return the result combined in a way that is different than just ‘field, comma/tab, field’. Concatenation is a way to make a query on several fields pushing them into one “column”. The character “+” or “||” can be used to concatenate in a query when used in ExecuteSQL. I found that “||” ( the double pipe) worked every time, the “+” was a little more particular.

/* concatenate with "+" test */

Let (
    [ $query = " SELECT LastName+', '+FirstName
        FROM myContacts
        WHERE LastName LIKE ?
            AND FirstName LIKE ? "

    ; $result = ExecuteSQL ( $query 
        ; "" ; "" 
        ; "Ab%" ; "A%" )

    ]; $result
) // SUCCESS: Abanour, Alyce
/* concatenate, place returns between fields */

Let (
    [ $query = " SELECT 
        FirstName + ' ' + LastName 
        , Address
        , City + ', ' + State + ' ' + Zip
        , '---'
        FROM myContacts
        WHERE LastName LIKE ?
            AND FirstName LIKE ? 
        ORDER BY state, zip "

    ; $result = ExecuteSQL ( $query 
        ; Char(13) ; Char(13) 
        ; "Ab%" ; "A%" )

    ]; $result
) // SUCCESS - ready to send to labels
/* concatenate with "||" test */

Let (
    [ $query = " SELECT LastName||',    '||FirstName
        FROM myContacts
        WHERE LastName LIKE ?
            AND FirstName LIKE ? "

    ; $result = ExecuteSQL ( $query 
        ; "" ; "" 
        ; "Ab%" ; "A%" )

    ]; $result
) // SUCCESS: Abanour,    Alyce

Calculations can be performed on the columns and results returned to a new column with a named alias.

/* calculate qty x price - note: 4 columns will be returned */

Let (
    [ $query = " SELECT 
        productID
        , qty
        , price
        , qty*price AS extendedPrice
        FROM lineItems
        WHERE orderID = ? "

    ; $result = ExecuteSQL ( $query 
        ; char(9) ; "" 
        ; orders::orderID )

    ]; $result
) // no example in sample database

The following Operators are valid in ExecuteSQL(). Mathematical errors (division by zero, for example) are not valid in ExecuteSQL(). And, of course, these operators are meant to work on fields/columns that return numeric values.

Adding Literals

The spacing in the Concatenation tests above uses literals. Any text can be in single quotes. Numbers do not need to be quoted. Constants are another form of Literal that might be used in the WHERE clause (search criteria). SQL functions can also be included in the query.

/* literal test */

Let (
    [ $query = "SELECT 'ABC123-' || zip || ' ' AS lit_text,
        123, CURRENT_DATE AS cur_date
        FROM myContacts
        WHERE State = ? 
        ORDER BY zip DESC "

    ; $result = ExecuteSQL ( $query 
        ; Char(9) ; "" 
        ; "WA" )

    ]; $result
) // SUCCESS

Using SQL Functions

SQL has some functions that maybe used in the queries. Each SQL db may have a different set of functions. The last query contains the System function “CURRENT_DATE” and returns the date in the “YYYY-MM-DD” format. Other System functions that work within ExecuteSQL:

  • CURRENT_TIME = “hh:mm:ss” (24-hour time)
  • CURRENT_TIMESTAMP = “YYYY-MM-DD hh:mm:ss” (24-hour time)
  • CURRENT_USER = returns same as the Get ( AccountName ) function in FileMaker

Date & Time functions that work well in ExecuteSQL() and may be used with the System functions, above or your date fields or even the properly formatted Date/Time text (in single quotes ‘YYYY-MM-DD hh:mm:ss’):

date_functions

/*
Removes duplicates based on listed fields.
And extract just a Year part from a date field
*/

Let (
   [ $query = " SELECT DISTINCT YEAR(s."date") 
      FROM sales_related_sales AS s 
      WHERE s.amount > ? 
      ORDER BY  s.amount DESC "

   ; $result = ExecuteSQL ( $query 
      ; "" ; "" ; 450 )

  ]; $result
)

DAYNAME() returns “Monday”, “Tuesday”, etc. and DAYOFWEEK() return a number (Sunday = 1).

String functions, such as LOWER & UPPER are most useful when needed to change the case of a field to test with the LIKE comparison in the WHERE clause. Since the comparison is case sensitive, a search for ” LIKE ‘A%’ ” will only return the column/field that begins with the capital letter “A”. Any field that begins with “a” will not be in the result. The string functions can also be used in the SELECT to change the case of the result:

/* test upper and lower */

Let (
    [ $query = " SELECT LOWER(FirstName), LastName 
        FROM myContacts 
        WHERE UPPER(LastName) LIKE ? "

    ; $result = ExecuteSQL ( $query 
        ; Char(9) ; "" 
        ; "AA%" )

    ]; $result
) // SUCCESS - christen    Aalund
/* LOWER() test */

Let (
    [ $query = " SELECT LOWER(lastname+', '+firstname)
        FROM mycontacts
        WHERE lastname LIKE ?
            AND firstname LIKE ? "

    ; $result = ExecuteSQL ( $query 
        ; "" ; "" 
        ; "Ab%" ; "A%" )

    ]; $result
) // SUCCESS: abbott, ashley

There are many SQL string functions that have not been tested for this article. Some string functions have been tested, but do not work as expected. The SQL string functions that seem to work well with Execute SQL:

string_functions

/* SUBSTRING() test.
this is similar to FMP Middle() function */

Let (
    [ $query = " SELECT SUBSTRING(lastname,1,4), firstname
        FROM mycontacts
        WHERE lastname LIKE ?
            AND firstname LIKE ? "

    ; $result = ExecuteSQL ( $query 
        ; "" ; "" 
        ; "Ab%" ; "A%" )

    ]; $result
) // SUCCESS

Aggregate (summary) SQL function work with ExecuteSQL and return the same results as the summary fields in FileMaker. If fields (other than used in aggregate functions) are to be returned in the result, the GROUP BY clause is used in the query to name the other fields.

aggregate_functions

/* 
SQL SUM function - if other fields are requested, 
they must be listed in a GROUP BY clause
Aggregates, are also be used in the HAVING clause, 
if comparing summary results.
SUM returns the Total amount & an alias is assigned to the column,
so that it can be used in the sort.
*/

Let (
    [ $query = " SELECT '#'||s.salespersonID
        , SUM( s.amount ) AS sum_amount
        FROM sales_related_sales AS s 
        GROUP BY s.salespersonID 
        ORDER BY sum_amount DESC "

    ; $result = ExecuteSQL( $query ; ",  "; "" )

    ]; $result
) // SUCCESS

Some SQL Math functions that may or may not work with ExecuteSQL (untested for this article):

math_funtions

/* SQL function ROUND(number,decimal_places)
does not return an error, but does not alter format
of number in the result.
*/

Let (
    [ $query = " SELECT s."date", ROUND(s.amount,2) AS amt_fmt
        FROM sales_related_sales AS s
        WHERE s.salespersonID = ?
            AND s.amount >= ?
        ORDER BY s.amount DESC "

    ; $header = "date" & char(9) & "amount¶"

    ; $result = TextStyleAdd($header;Bold) & 
        ExecuteSQL ( $query ; char(9) ; "" 
        ; salesperson::salespersonID ; 100 )

    ]; $result
)// does NOT round to two decimals, but does not fail

SQL Logical Functions

The CASE SQL function, much like the FileMaker Case() function, allows multiple tests and results & includes an optional default. The SQL CASE function for use in ExecuteSQL has two variations:

  1. Simple CASE expression – this uses the expression as in input and the values are used in the WHEN ‘test’
    CASE input
        WHEN value1 THEN result1
        { WHEN value2 THEN result2 }
        { ... }
        { ELSE result3 }
    END
  2. Searched CASE expression – each ‘test’ expression can be different.
    CASE
        WHEN expr1 THEN result1
        { WHEN expr2 THEN result2 }
        { ... }
        { ELSE result3 }
    END

There are two other logical functions found in some SQL systems: IIF [ or IF ] and CHOOSE. None of these worked with ExecuteSQL(), so the suggestion to use the CASE is presented in the example file.

/*
Since CASE works and IIF or IF don't...
*/

Let (
   [ $query = " SELECT p.name, 
        CASE 
            WHEN p.salespersonID > 3 
            THEN 'x' 
            ELSE 'o' 
        END, p.salespersonID
        FROM salesperson AS p "

   ; $result = ExecuteSQL ( $query ; ", " ; "" )

   ]; $result
) // SUCCESS
/*
CHOOSE ( index, val_1, val_2 [, val_n ] )
- Returns the item at the specified index from a list of values.
does not work, so changed to CASE
*/

Let (
   [ $query = " SELECT s.year_month, s.salespersonID, s.amount, 
      CASE MONTH(s."date")
         WHEN 1 THEN 'JAN'
         WHEN 2 THEN 'FEB'
         WHEN 3 THEN 'MAR'
         WHEN 4 THEN 'APR'
         WHEN 5 THEN 'MAY'
         WHEN 6 THEN 'JUN'
         WHEN 7 THEN 'JUL'
         WHEN 8 THEN 'AUG'
         WHEN 9 THEN 'SEP'
         WHEN 10 THEN 'OCT'
         WHEN 11 THEN 'NOV'
         WHEN 12 THEN 'DEC'
         END
      FROM sales_related_sales AS s
      WHERE LEFT(s.year_month,4) = '2010'
          AND s.salespersonID = 1 "

   ; $result = ExecuteSQL ( $query ; ", " ; "" )

   ]; $result
) // since the CHOOSE() function doesn't work, 
// this was revised to use the CASE function

Find Criteria is in the WHERE Clause

For find criteria, the values are case sensitive or they will not match in SQL. Changing the field Options, Storage, Indexing, Default language settings may not help. “A” is not the same as “a” when making SQL queries.

default_language

Comparison Operators are used in the WHERE clauses. Just as these are used in the relationship graph when connecting two or more fields, these make straight comparisons between fields, fields and variables, or fields and constants:

SQL has some special comparison operators.

between

The AND & OR operators may be used (in any combination) to narrow down your choices. If making more than one “request” a single WHERE clause uses multiple AND & OR operators. These requests can be nested and inserted in parenthesis to make the intend clear.

 WHERE ( x = 1 OR y = 2 ) // either can be true
        AND ( z = 3 ) // and this must be true
 WHERE s.sales_date IS NOT NULL

ins_null

    WHERE s.sales_date != NULL
Let (
    [ $query = " SELECT s.salesPersonID, s."date", s.amount
        FROM sales_related_sales AS s
        WHERE s."date" BETWEEN ? AND ?
        ORDER BY s.salesPersonID DESC, s."date" "

    ; $header = "Sales Between 2010-01-01 and " & 
         Date(7; 0 ; 2010 ) & "¶"
    ; $header = $header & "ID" & char(9) & "sales¶"

    ; $result = ExecuteSQL ( $query ; char(9) ; "" 
        ; "2010-01-01" 
        ; Date(7; 0 ; 2010 ) )

    ]; TextStyleAdd($header;Bold) & $result
) // the dates are supplied as "literal" (yyyy-mm-dd) 
 // and with FileMaker Date functions

LIKE with Wildcards

The LIKE keyword in a WHERE clause gives us the ability to use some wildcard characters similar to the use of symbols in FileMaker find requests. The two symbols (characters) that work with ExecuteSQL are the “%” (percent character, meaning one or more) and “_” (underscore, meaning one character). These can be used anywhere within the string to be compared. The “%” wildcard was used in several other examples in this article.

/* using Wildcard "_" in LIKE */
Let (
    [ $query = " SELECT firstname, lastname FROM mycontacts
        WHERE lastname LIKE ?
        ORDER BY lastname "

    ; $result = ExecuteSQL ( $query 
        ; "" ; "" 
        ; "A_a%" ) // test for uppercase A
       // followed by any character
       // followed by lowercase a
    ]; $result
) // SUCCESS

WHERE … IN ( { SELECT … } )

The IN keyword used for the WHERE clause, takes a comma-delimited list and searches by each of the values (an OR search). If the list is composed on numbers, the list is just comma-delimited. If the list is TEXT, then the values, must be enclosed with single quotes (‘abc’,’def’,’ghi’,’jkl’). These values will be automatically be quoted as needed, if you nest another SELECT inside. Only one column/field should be returned in the nested SELECT results.

/*
WHERE ... IN not dynamic
*/

Let (
   [ $query = " SELECT firstname,lastname,city,state
      FROM myContacts
      WHERE state IN ('MI','IN','OH') 
         AND LOWER(lastname) LIKE 'z%' 
         AND LOWER(firstname) LIKE 'a%' 
      ORDER BY state, city, lastname "

   ; $result = ExecuteSQL ( $query 
      ; ", " ; "" 
      )

   ]; $result
) // SUCCESS
/*
SELECT WHERE ... IN () with ? arguments
*/

Let (
   [ $query = " SELECT firstname,lastname,city,state
      FROM myContacts
      WHERE state IN (?, ?, ?) 
         AND LOWER(lastname) LIKE 'z%' 
         AND LOWER(firstname) LIKE 'a%' 
      ORDER BY state, city, lastname "

   ; $result = ExecuteSQL ( $query 
      ; ", " ; "" 
      ; "MI" ; "IN" ; "OH" )

   ]; $result
) // same query as above, with the arguments 
// as multiple inside the IN

An example of nested SELECTs uses two tables (unrelated, but having a common key). This could be done with a JOIN, but is just a demonstration of finding one column for use in the WHERE…IN. This example finds all the distinct salespersonID in the sales records. It narrows down the list by finding those who had sales in the year 2009. That “list” gets put into the IN keyword for use with the outer SELECT. The results listed those salespersons who had sales in 2009.

/*
nesting SELECTS for WHERE clause
*/

Let (
   [ $query = " SELECT  
      s.name
      FROM salesperson AS s
      WHERE s.salespersonID IN (
         SELECT DISTINCT
           sales.salespersonID
         FROM sales_related_sales AS sales
         WHERE YEAR(sales."date") = ?
         )
      ORDER BY s.name "

   ; $result = ExecuteSQL ( $query
      ; ", " ; "" 
      ; 2009 )

   ]; $result 
) //

SELECT DISTINCT

Return unique values by using the DISTINCT keyword with SELECT. Some of the SQL functions may also use the DISTINCT keyword, but have been untested for this article. Example uses of DISTINCT are found here and in other articles. If you use more than one field/column for your SELECT, the DISTINCT will consider them all.

/*
Removes duplicates based on listed field(s)
*/

Let (
   [ $query = " SELECT DISTINCT s.amount 
      FROM sales_related_sales AS s 
      WHERE s.amount >= ? 
      ORDER BY  s.amount DESC "

   ; $result = ExecuteSQL ( $query 
      ; "" ; "" 
      ; 490 )

   ]; $result
)
/*
Removes duplicates based on listed field(s)
*/

Let (
   [ $query = " SELECT DISTINCT lastname
      FROM myContacts 
      WHERE LOWER(lastname) LIKE ? 
      ORDER BY  lastname "

   ; $result = ExecuteSQL ( $query 
      ; "" ; "" 
      ; "ab%" )

   ]; $result
) // distinct lastname returns 192
// when distinct lastname, firstname is used, 
// 2100 records are returned

HAVING is a Special WHERE for Aggregate fields

When you want to narrow your search based on the sum or count or other aggregate, the HAVING clause is used. Some SQL systems allow you to enter the aggregate in the SELECT statement and assign an alias to the new column. This can be used in the HAVING clause with just the alias. However, ExecuteSQL does not seem to allow this, so just repeat the aggregate in the HAVING clause, too, as shown in the examples below.

/* 
HAVING used to filter 
*/

Let (
   [ $query = " SELECT s.salespersonID
      , AVG( s.amount ) AS avg_amt
      FROM sales_related_sales AS s 
      GROUP BY s.salespersonID 
      HAVING AVG( s.amount ) > ? "

   ; $result = ExecuteSQL ( $query 
      ; ",  "; "" 
      ; 300 )

   ]; $result
) // SUCCESS - using "?" in select clause for a constant
/* 
Using HAVING
*/

Let (
   [ $query = " SELECT '#'||s.salespersonID
      , SUM( s.amount ) AS sum_amount
      FROM sales_related_sales AS s 
      GROUP BY s.salespersonID 
      HAVING SUM( s.amount ) > 150000 "

   ; $result = ExecuteSQL( $query 
      ; ",  "; "" )

   ]; $result
) // the HAVING clauses repeats the aggregate

JOINS & UNIONS

JOINS are what you do when you place two table occurrences on the Relationship Graph in FileMaker and connect two fields together. In SQL you simply name the two tables and add the relationship to the WHERE clause. The use of table ALIAS is more apparent when you start using joins. If you have more than one field/column with the same name, you must specify which table for each field, or you will get a SQL syntax error.

   FROM salesperson AS s, sales_related_sales AS sales
   WHERE s.salespersonID = sales.salespersonID
or
   FROM salesperson, sales_related_sales
   WHERE filter_date = year_month

These are IMPLICIT JOINS. All tables are listed, the “join” is implied, and the simple equality is defined in the WHERE clause. Any of the comparison operators could be used, along with AND & OR. If you do not specify the WHERE clause at all, you get a Cartesian relationship. You probably rarely want Cartesian joins, but when you do (for returning globals, perhaps?) an example is shown here:

/*
JOINS - cartesian - globals
*/

Let ( 
   [ $query = " SELECT s.name, s.salespersonID
      , global_num_g, global_txt_g
      FROM salesperson AS s, dev "

   ; $result = ExecuteSQL ( $query
      ; ", " ; ""
      )

   ]; $result
) // no relationship. all records from both are returned

INNER JOINS are the same as the implied equi-join (WHERE using the “=” matches, above), their syntax is slightly different.

   FROM salesperson AS s JOIN sales_related_sales AS sales
   ON s.salespersonID = sales.salespersonID
or
   FROM salesperson JOIN sales_related_sales
       ON filter_date = year_month
or
   FROM salesperson AS s 
      INNER JOIN sales_related_sales AS sales
      ON s.salespersonID = sales.salespersonID
or
   FROM salesperson INNER JOIN sales_related_sales
       ON filter_date = year_month

They all work as well. The difference may be apparent if you have more than two files and must join them in a way that connects them accurately. An example would be Clients, Invoices & Invoice_items. Just as you put links between these tables on the FileMaker relationship graph, you link them with JOINs in SQL.

   FROM Clients AS c 
      JOIN Invoices AS inv ON c.clientID_pk = inv.clientID_fk
         AND ( c.state = 'WA' OR c.state = 'ID' )
      JOIN Invoice_items AS itm 
         ON inv.invoiceID_pk = itm.invoiceID_fk
         AND inv.invoice_date = '2009-10-13'
or
   FROM Clients AS c 
      JOIN Invoices AS inv ON c.clientID_pk = inv.clientID_fk
      JOIN Invoice_items AS itm 
         ON inv.invoiceID_pk = itm.invoiceID_fk
   WHERE ( c.state = 'WA' OR c.state = 'ID' )
      AND inv.invoice_date = '2009-10-13'
or (implied):
   FROM Client c, Invoices inv, Invoice_items inv
   WHERE c.clientID_pk = inv.clientID_fk
      AND inv.invoiceID_pk = itm.invoiceID_fk
      AND ( c.state = 'WA' OR c.state = 'ID' )
      AND inv.invoice_date = '2009-10-13'

The advantage may be the clear separation, so you understand what’s being queried. But these should return the same results.

OUTER JOINS are clearly different. Sometimes you want all of a “left-side” of a relationship AND any related records. This is similar to showing a portal on a layout. Find all records and some portals may be empty. This is a LEFT OUTER JOIN. You must specify the LEFT or RIGHT keyword when using OUTER JOIN. There are some articles on OUTER JOINS (see below).

   FROM Clients AS c 
      LEFT OUTER JOIN Invoices AS inv 
         ON c.clientID_pk = inv.clientID_fk
         AND ( c.state = 'WA' OR c.state = 'ID' )
      LEFT JOIN Invoice_items AS itm 
         ON inv.invoiceID_pk = itm.invoiceID_fk
         AND inv.invoice_date = '2009-10-13'
// May return different records than just with "JOIN"
// or "INNER JOIN"!

UNION is a keyword for combining two (or more) tables with the same number of fields in each SELECT statement. Perhaps you have archived records in a separate table and need to summarizes records from a current and archived table in one report.

Tips, References & Resources

FileMaker Inc.

General SQL tutorials

ExecuteSQL Blogs & Articles

Helpful Databases and Custom Functions

 FileMaker SQL Plug-ins

If you want to expand your knowledge of SQL and the additional commands you can make with plug-ins, try these:

  • Dracoventions SQL Runner – SQL Runner is a free FileMaker Pro and Server database plug-in that lets you read and write data from and to FileMaker in powerful new ways.
  • Youseful SQL Plugin – lets you run SQL queries directly on your FileMaker database.
  • 360Works JDBC plugin – allows execution of arbitrary SQL statements on one or more JDBC databases, iterating result sets, and importing from any database which supports the JDBC protocol.
  • 360Works Script Master – Direct access to the FileMaker SQL engine with ScriptMaster 4 Advanced, which allows you to execute SQL commands directly from any script.
  • myFMbutler DoSQL plug-in – a FileMaker Pro plug-in for Windows and Macintosh that allows you to to manipulate FileMaker data from FileMaker calculations.
  • MBS SQL Connections – contains functions to access SQL database servers directly.
  • Goya – BaseElements PluginBE_FileMakerSQL
  • CNS – MMQuery – MMQuery_ExecuteSQL: This function allows you to use SQL statements to run queries against the current Database File. MMQuery_ExecuteSQLEx: This function is only available in FileMaker Pro 11 and above. This “extended” version of MMQuery_ExecuteSQL allows you to optionally specify a separate, open Database File.
  • SmartPill PHP edition – using the internal SQL functions (fm_sql_execute or fm_sql_select).

Troubleshooting

If an error occurs during query parsing or execution, FileMaker Pro returns “?”. Although not an argument, unless you disagree that you have entered your queries correctly, it shows an error. The Error Codes are usually found in the FileMaker Help topic, FileMaker Pro 12 Error Code Reference Guide.

  1. Try the query in one of the example databases first.
  2. Paste the query into the Data Viewer (FileMaker Pro Advanced 12) to see any errors that might be generated.
  3. Wrap the query in “EvaluationError ( Evaluate ( $query ) )” in the Data Viewer or as a Set Field to see any error codes returned.
  4. There may be no errors, but the results don’t appear as you expect. A SQL function may not be used by the ExecuteSQL at this time, for example.
  5. It may be a formatting “error” that cannot be solved directly, or you need to change your delimiters.
  6. Test the query in a Let() statement and assign variables as needed. If you use this in a Set Field script step, you can see what the variables are in the Data Viewer.
/* sample query used in the enclosed demo */
Let (
   [ $query = "
        // your SELECT statement goes here!
        "
   ; $result = ExecuteSQL ( $query 
       ; "" ; "" 
       ; "" // optional arguments
       )

   ]; $result
) // you may add other variables for use with the final $result

DEMO FILES:  SQL4_fmdev2.fmp12, related_sales.fmp12 The first demo file has been updated with new SQL queries.

A larger file with one million records was used for the contacts demos. It was too large to upload here, so you may import this smaller set: 4145Names

The Missing FM 12 ExecuteSQL Reference.pdf (new PDF version of this article 01 NOV 2012)

49 thoughts on “The Missing FM 12 ExecuteSQL Reference

  1. Bruce Herbach

    Beverly, Kevin,

    This is a fantastic reference. Thank you , Thank you, Thank you…

    I have been looking for something like this since 12 came out.

    Bruce

    Reply
  2. HOnza

    Thanks, just bookmarked as my new #1 FileMaker SQL reference!

    BTW, is using double quotes instead of reverse single quotes for quoting table and field names a difference between FileMaker’s implementation of SQL and other (such as MySQL) implementations, or are both options valid?

    Reply
    1. Beverly Voth Post author

      No, the ‘tick’ (reverse single quote) is not valid for ExecuteSQL. I know that MySQL uses it, but MS SQL does not. The only reason to double quote tables and fields in ExecuteSQL is because they are invalid names (spaces or reserved keywords or other characters). If you stick to AlphaNumeric and “_” underscore, you never have to quote your names in ExecuteSQL. (don’t start with “_” or numbers either!)

  3. Beverly Voth Post author

    I thought this should be a PDF! Anyone else want that, too? I think I’ll give it a few weeks to shake out any additions/omissions, then produce a PDF.

    Reply
  4. john renfrew

    Beverley

    GREAT work – this is a brilliant starting point for a serious white paper, thank you for the effort.

    COALESCE works (V COOL), CAST works, CEIL works, LTRIM and RTRIM works, TRIM only seems to trim one character, SPACE works,

    and the maths functions that DO NOT work are ASIN CONV COT CRC FORMAT LOG100 OCT POWER RAND SQUARE TRUNCATE
    all the rest do

    Reply
    1. Beverly Voth Post author

      Thanks, John! Yes. this needs to be a White Paper (as a PDF!)

      ROUND() did not error, but did not perform the rounding, did you find the same to be true?

  5. john renfrew

    Using the W3 schools try it, if you ROUND(1.234, 2) it returns 1.23 and if you ROUND(1,2) it returns 1 – with no trailing zeros which is because

    >>from the MYSQL reference manual
    The return type is the same type as that of the first argument (assuming that it is integer, double, or decimal). This means that for an integer argument, the result is an integer (no decimal places)

    but ROUND(1.000, 2) will be 1 as it also removes trailing zeros, and does not add them in the case of ROUND(1.23, 5)

    Reply
    1. Beverly Voth Post author

      Hello, David! remember that ExecuteSQL function is just one part of FileMaker. Can you post process the results with some of FileMaker’s other functions? or are you trying to return several columns? If so, what are you doing with the results? The numbers returned are correct, but as you say no trailing zeros (from the ROUND SQL function, I’m assuming).

  6. Daniel Shanahan

    Brilliant! Thank you, Beverly. This is a great resource. I add my vote for a .pdf.

    Kevin, although you mentioned that this is 100% Beverly’s work, your site has become the de facto place for ExecuteSQL() info, at least for me. Thank you.

    Reply
  7. Olly Groves

    Great stuff Beverly, very useful.

    Imagine FM Inc would be open for this to become an official white paper.

    In meantime as fyi (to all readers) PDF bookmarklet services like http://www.printfriendly.com are pretty good. (has a wordpress plugin/button maker too which might be option for site/page)

    Reply
  8. Barb Levine

    Thanks to Bev for an excellent resource, and to Kevin for hosting Bev’s work. Yes, a PDF would be fabulous!

    And in the meantime, thanks to Olly for introducing me to the Print Friendly tool.

    Reply
  9. Joel Shapiro

    Thanks Bev, this is awesome!

    I didn’t know reserved words could be used as field names if they’re inside quotes. I’d just changed a field name in a new project from “Option” to something less ideal; now I can change it back! :-)

    -Joel

    Reply
    1. Beverly Voth Post author

      I don’t recommend changing to (or using) reserved words at all. It may break somewhere else, Joel (ODBC publishing, web publishing, ???)! Try “my_option” or something appropriate. You preserve the “option”, but make it less fragile!

  10. Josh Ormond

    What does it all mean?

    Nice article, Bev. You should throw in a couple pics of wildlife, just so you can say it works “in the wild”.

    Reply
  11. Tom Fitch

    Thanks so much for this, Bev. I know I’ll be referring to this often.

    I was wondering about the use of script variables in your Let statements. Was that deliberate? To me it was a little distracting… a minor quibble.

    This article kicks a$$!

    Reply
    1. Beverly Voth Post author

      Yes, Tom, there was a deliberate use of variables. For most of the queries, it didn’t matter, but the complex ones can be seen in the Data Viewer for debugging! Thanks!

  12. JustinC

    Bev,
    You mention that the placeholder (“?”) can be used in ANY part of the query. I thought I had tried to use it liberally in the past and had had troubles, so I tried again with some simple queries. I WAS able to use it in the SELECT clause, but not in the FROM clause. Do you have examples of successful usage in the FROM clause?

    Reply
    1. Beverly Voth Post author

      Hmm, I thought I had it. Maybe I was using the custom functions in there at one point. And those clearly are not using the ? – argument.

      Let (

      [ $from = "dev"

      ; $query = " SELECT *
      FROM ? "

      ; $result = ExecuteSQL ( $query
      ; ", " ; ""
      ; $from )

      ]; $result
      ) // FAIL - cannot use the argument in FROM clause.

      The error I got with the above is the 8310 and sometimes that means the query isn’t quite right. I couldn’t fix it, So I’m mark that answer as a NO, only where there are values or constants. Even field names can’t be passed as arguments, because they become literals.

      I did not however try the GetFieldName(fieldName), but I don’t think that will work either.

  13. Martin Spanjaard

    Very nice indeed! Thanks. Lots to learn.
    A question about speed. The moment we learned that ExecuteSQL was available in FMP 12 my colleague Peter (who was SQL savvy already) and I started to do speed tests, comparing ExecuteSQL with FMP native.
    Our results were that ExecuteSQL was 4 to 10 times slower. Is this your impression too, or did we do something wrong?

    Reply
    1. Beverly Voth Post author

      Martin, this article expressed no opinion on:

      • when to use ExecuteSQL vs. other native FileMaker find, sort & report methods
      • performance differences between ExecuteSQL and other native FileMaker find, sort & report methods
      • what to DO with the results of using ExecuteSQL

      The links to other resources may have some of these answers already or perhaps this question is an entirely different article!

    2. JustinC

      Martin,
      I haven’t done formal testing like you have apparently done, but I have noticed that in certain situations ESQL can be slower than native functions. Most of the time it seems rather comparable, however, and I haven’t seen the magnitude of difference that you reported. There have been a few instances where I have found ESQL to be a LOT slower, as you noticed, but that always seemed to be with the way the query was formed. (I don’t think that the SQL parser in FM is very good.)
      For example, I was trying to use ‘Between’ for a query (“..WHERE .myDate BETWEEN 8/1/2012 AND 10/1/2012) and it was taking a long time and pulling a lot of data. But I then switched my WHERE clause to a simple “…WHERE myDate 8/1/2012″.
      I vaguely recall having similar issues with “GROUP BY ” clauses; I think that in certain cases the server is NOT doing the filtering and grouping, and instead is sending your client the entire data set and then relying on the client to do the sorting. The statement using GROUP BY took a really long time (30 seconds?), but if I eliminated that clause it only took a few seconds.

  14. Martin Spanjaard

    There are two reasons to hope for such an article:
    1. to learn the do’s and dont’s when writing SQL for FMP;
    2. and if our speedtests are to be confirmed by others, that FileMaker Inc. will optimize the SQL interpreter in FMP.

    Reply
  15. Rick Whitelaw

    Bev,

    I’ve been reading so much about this function and the advantages of using it. However I know zip about SQL. This article has more than served to get me started. Thanks so much.

    Rick.

    Reply
  16. Peter Beehler

    Has anyone manage to be able to use a case insensitive version of the GROUP BY command. I use the ExecuteSQL most frequently to summarize data that would otherwise require new relationship joins etc and otherwise add clutter to the relationship graph.

    Reading elsewhere it looks like you should be able to simply use upper or lower, but this doesn’t seem to work when grouping the data. From reading further, the format can be a little engine specific and varies in mysql verus MS SQL.

    Seems like the following should work but doesn’t, to group totals quantities by mfg on an invoice

    ExecuteSQL ( “SELECT lower(mfg), sum(Qty)

    FROM LineItems as lineitems

    WHERE lineitems.invoicenumber=”&x&”

    GROUP BY lower(mfg)

    ORDER BY lower(mfg)

    “; ” | “;”¶”

    )
    )

    If for example I have some lineitem on an invoice data

    Line 1, mfg = A, qty = 1
    Line 2, mfg = A, qty = 2
    Line 3, mfg = B, qty = 3
    Line 4, mfg = b, qty= 4

    The result I get is
    A | 3
    B | 3
    b | 4

    if I use lower in select statement then I get

    a | 3
    b | 3
    b | 4

    if I use lower in the GROUP BY lower(type) statement then I get ? , so it obviously doesn’t like the syntax.

    Great article Beverly, it has proved most hopeful.

    Reply
    1. Beverly

      Did you get the PDF (contains much more than the article on this page) and updated examples DB? :)

      Did you try to ALIAS the field and use that in the GROUP BY and ORDER BY?

      SELECT lower(mfg) AS lowmfg

      ….
      GROUP BY lowmfg
      ORDER BY lowmfg

      I haven’t tested these, but it may be worth a shot!

      Yes, I looked for MS SQL, ORACLE and MySQL functions to test for the article & example file. Yes, they can be different depending on the DB!

  17. Peter Beehler

    Thanks for lightning fast response, I did get the PDF thanks.
    I like your alias idea, but it didn’t work when I tested it.

    Reply
  18. Peter Beehler

    Yes that’s the work around I’ve been using, just hoping you might know the answer to avoid this step that just dirties up the database schema.

    Reply
  19. david regan

    Is it possible to have a custom order by I have tried several ways:

    ORDER BY
    p.id (3, 4, 1, 2 )

    ORDER BY
    (p.id, 3, 4, 1, 2 )

    Reply
    1. Beverly Voth Post author

      David, if I understand you correctly, you are trying to custom sort by the listed match for the PID. No, sorry that SQL uses the field names for the sorting and only ASC or DESC are used. (at least I’ve not found any other!) You might try setting a calculated field to the “match” and sort by that:
      mysort = CASE ( p:id = 3 ; 1
      ; p:id = 4 ; 2
      ; p:id = 1 ; 3
      ; p:id = 2 ; 4
      )
      Then ORDER BY p.mysort

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s