FileMaker 13 ® SQL Reference
© 2013 FileMaker, Inc. All Rights Reserved. FileMaker, Inc. 5201 Patrick Henry Drive Santa Clara, California 95054 FileMaker and Bento are trademarks of FileMaker, Inc. registered in the U.S. and other countries. The file folder logo, FileMaker WebDirect, and the Bento logo are trademarks of FileMaker, Inc. All other trademarks are the property of their respective owners. FileMaker documentation is copyrighted.
Contents Chapter 1 Introduction About this reference Where to find PDF documentation About SQL Using a FileMaker database as a data source Using the ExecuteSQL function Chapter 2 Supported standards Support for Unicode characters SQL statements SELECT statement SQL clauses FROM clause WHERE clause GROUP BY clause HAVING clause UNION operator ORDER BY clause OFFSET and FETCH FIRST clauses FOR UPDATE clause DELETE statement INSERT statement UPDATE statement CREATE TABLE statement ALTER TABLE statement CREATE
SQL functions Aggregate functions Functions that return character strings Functions that return numbers Functions that return dates Conditional functions Reserved SQL keywords Index 28 28 29 30 32 32 34 37
Chapter 1 Introduction As a database developer, you can use FileMaker Pro to create database solutions without any knowledge of SQL. But if you have some knowledge of SQL, you can use a FileMaker database file as an ODBC or JDBC data source, sharing your data with other applications using ODBC and JDBC. You can also use the FileMaker Pro ExecuteSQL function to retrieve data from any table occurrence within a FileMaker Pro database.
Chapter 1 | Introduction 6 Using a FileMaker database as a data source When you host a FileMaker database as an ODBC or JDBC data source, FileMaker data can be shared with ODBC- and JDBC-compliant applications. The applications connect to the FileMaker data source using the FileMaker client drivers, construct and execute the SQL queries using ODBC or JDBC, and process the data retrieved from the FileMaker database solution.
Chapter 2 Supported standards This reference describes the SQL statements and constructs supported by FileMaker. The FileMaker ODBC and JDBC client drivers support all of the SQL statements described in this chapter. The FileMaker Pro ExecuteSQL function supports only the SELECT statement. Use the client drivers to access a FileMaker database solution from an ODBC- or JDBC-compliant application. The FileMaker database solution can be hosted by either FileMaker Pro or FileMaker Server.
Chapter 2 | Supported standards 8 SELECT statement Use the SELECT statement to specify which columns you're requesting. Follow the SELECT statement with the column expressions (similar to field names) you want to retrieve (for example, last_name). Expressions can include mathematical operations or string manipulation (for example, SALARY * 1.05). The SELECT statement can use a variety of clauses: SELECT [DISTINCT] {* | column_expression [[AS] column_alias],...} FROM table_name [table_alias], ...
Chapter 2 | Supported standards 9 SQL clauses The ODBC and JDBC client drivers provide support for the following SQL clauses. Use this SQL clause To FROM (page 9) Indicate which tables are used in the SELECT statement. WHERE (page 10) Specify the conditions that records must meet to be retrieved (like a FileMaker Pro find request). GROUP BY (page 11) Specify the names of one or more fields by which the returned values should be grouped.
Chapter 2 | Supported standards 10 If you are joining more than one table, and you want to discard all rows that don’t have corresponding rows in both source tables, you can use INNER JOIN. For example: SELECT * FROM Salespeople INNER JOIN Sales_Data ON Salespeople.Salesperson_ID = Sales_Data.Salesperson_ID If you are joining two tables, but you don’t want to discard rows of the first table (the “left” table), you can use LEFT OUTER JOIN.
Chapter 2 | Supported standards 11 GROUP BY clause The GROUP BY clause specifies the names of one or more fields by which the returned values should be grouped. This clause is used to return a set of aggregate values. It has the following format: GROUP BY columns columns must match the column expression used in the SELECT clause. A column expression can be one or more field names of the database table separated by commas. Example The following example sums the salaries in each department.
Chapter 2 | Supported standards 12 UNION operator The UNION operator combines the results of two or more SELECT statements into a single result. The single result is all of the returned records from the SELECT statements. By default, duplicate records are not returned. To return duplicate records, use the ALL keyword (UNION ALL).
Chapter 2 | Supported standards 13 OFFSET and FETCH FIRST clauses The OFFSET and FETCH FIRST clauses are used to return a specified range of rows beginning from a particular starting point in a result set. The ability to limit the rows retrieved from large result sets allows you to “page” through the data and improves efficiency. The OFFSET clause indicates the number of rows to skip before starting to return data. If the OFFSET clause is not used in a SELECT statement, the starting row is 0.
Chapter 2 | Supported standards 14 To return the ten rows and their peer rows (rows that are not distinct based on the ORDER BY clause): SELECT emp_id, last_name, first_name FROM emp ORDER BY last_name, first_name OFFSET 25 ROWS FETCH FIRST 10 ROWS WITH TIES FOR UPDATE clause The FOR UPDATE clause locks records for Positioned Updates or Positioned Deletes via SQL cursors.
Chapter 2 | Supported standards 15 Additional examples: Using Sample SQL text constant SELECT 'CatDog' FROM Salespeople numeric constant SELECT 999 FROM Salespeople date constant SELECT DATE '2012-06-05' FROM Salespeople time constant SELECT TIME '02:49:03' FROM Salespeople timestamp constant SELECT TIMESTAMP '2012-06-05 02:49:03' FROM Salespeople text column SELECT Company_Name FROM Sales_Data SELECT DISTINCT Company_Name FROM Sales_Data numeric column SELECT Amount FROM Sales_Data SELECT
Chapter 2 | Supported standards 16 Retrieving the contents of a container field: CAST() function and GetAs() function You can retrieve binary data, file reference information, or data of a specific file type from a container field. If file data or JPEG binary data exists, the SELECT statement with GetAS(field name, 'JPEG') retrieves the data in binary form; otherwise, the SELECT statement with field name returns NULL.
Chapter 2 | Supported standards 17 1 If the data was inserted using the Insert > Picture command, drag and drop, or paste from the clipboard, specify one of the file types listed in the following table. For example: SELECT GetAs(Company_Logo, 'JPEG') FROM Company_Icons File type Description File type Description 'GIFf' Graphics Interchange Format 'PNTG' MacPaint 'JPEG' Photographic images '.
Chapter 2 | Supported standards 18 expr is the list of expressions giving the values for the columns of the new record. Usually the expressions are constant values for the columns (but they can also be a subquery). You must enclose character string values in pairs of single quotation marks ('). To include a single quotation mark in a character string value enclosed by single quotation marks, use two single quotation marks together (for example, 'Don''t'). Subqueries must be enclosed in parentheses.
Chapter 2 | Supported standards 19 UPDATE statement Use the UPDATE statement to change records in a database table. The format of the UPDATE statement is: UPDATE table_name SET column_name = expr, ... [ WHERE { conditions } ] column_name is the name of a column whose value is to be changed. Several columns can be changed in one statement. expr is the new value for the column. Usually the expressions are constant values for the columns (but they can also be a subquery).
Chapter 2 | Supported standards 20 CREATE TABLE statement Use the CREATE TABLE statement to create a table in a database file. The format of the CREATE TABLE statement is: CREATE TABLE table_name ( table_element_list [, table_element_list...] ) Within the statement, you specify the name and data type of each column. 1 table_name is the name of the table. table_name has a 100 character limit. A table with the same name must not already be defined. The table name must begin with an alphabetic character.
Chapter 2 | Supported standards 21 1 To define a column as a container field, use BLOB, VARBINARY, or BINARY VARYING for the field_type. 1 To define a column as a container field that stores data externally, use the EXTERNAL keyword. The relative_path_string defines the folder where the data is stored externally, relative to the location of the FileMaker database. This path must be specified as the base directory in the FileMaker Pro Manage Containers dialog box.
Chapter 2 | Supported standards 22 Examples To Sample SQL add columns ALTER TABLE Salespeople ADD C1 VARCHAR remove columns ALTER TABLE Salespeople DROP C1 set the default value for a ALTER TABLE Salespeople ALTER Company SET DEFAULT column remove the default value for a column 'FileMaker' ALTER TABLE Salespeople ALTER Company DROP DEFAULT Note SET DEFAULT and DROP DEFAULT do not affect existing rows in the table, but change the default value for rows that are subsequently added to the table.
Chapter 2 | Supported standards 23 Dropping an index for any column automatically selects the Storage Option of None and clears Automatically create indexes as needed in Indexing for the corresponding field in the FileMaker database file. The PREVENT INDEX CREATION attribute is not supported. Example DROP INDEX ON Salespeople.Salesperson_ID SQL expressions Use expressions in WHERE, HAVING, and ORDER BY clauses of SELECT statements to form detailed and sophisticated database queries.
Chapter 2 | Supported standards 24 FileMaker also accepts SQL-92 syntax ISO date and time formats with no braces: 1 DATE 'YYYY-MM-DD' 1 TIME 'HH:MM:SS' 1 TIMESTAMP 'YYYY-MM-DD HH:MM:SS' The FileMaker Pro ExecuteSQL function accepts only the SQL-92 syntax ISO date and time formats with no braces. Constant Acceptable syntax (examples) Text 'Paris' Number 1.
Chapter 2 | Supported standards 25 Character operators You can concatenate characters. Examples In the following examples, last_name is 'JONES ' and first_name is 'ROBERT ': Operator Concatenation Example Result + Keep trailing blank characters first_name + last_name 'ROBERT JONES ' - Move trailing blank characters to the end first_name - last_name 'ROBERTJONES ' Date operators You can modify dates. Examples In the following examples, hire_date is DATE '2013-01-30'.
Chapter 2 | Supported standards 26 Operator Meaning EXISTS ‘True’ if a subquery returned at least one record ANY Compares a value to each value returned by a subquery (operator must be preceded by =, <>, >, >=, <, or <=); =Any is equivalent to In ALL Compares a value to each value returned by a subquery (operator must be preceded by =, <>, >, >=, <, or <=) Examples SELECT Sales_Data.Invoice_ID FROM Sales_Data WHERE Sales_Data.Salesperson_ID = 'SP-1' SELECT Sales_Data.
Chapter 2 | Supported standards 27 SELECT * FROM Sales_Data WHERE Sales_Data.Amount = ANY (SELECT Sales_Data.Amount FROM Sales_Data WHERE Sales_Data.Salesperson_ID = 'SP-1') SELECT * FROM Sales_Data WHERE Sales_Data.Amount = ALL (SELECT Sales_Data.Amount FROM Sales_Data WHERE Sales_Data.Salesperson_ID IS NULL) Logical operators You can combine two or more conditions.
Chapter 2 | Supported standards 28 The following example shows the importance of precedence: WHERE salary > 40000 OR hire_date > (DATE '2008-01-30') AND dept = 'D101' Because AND is evaluated first, this query retrieves employees in department D101 hired after January 30, 2008, as well as every employee making more than $40,000, no matter what department or hire date. To force the clause to be evaluated in a different order, use parentheses to enclose the conditions to be evaluated first.
Chapter 2 | Supported standards 29 Examples SELECT SUM (Sales_Data.Amount) AS agg FROM Sales_Data SELECT AVG (Sales_Data.Amount) AS agg FROM Sales_Data SELECT COUNT (Sales_Data.Amount) AS agg FROM Sales_Data SELECT MAX (Sales_Data.Amount) AS agg FROM Sales_Data WHERE Sales_Data.Amount < 3000 SELECT MIN (Sales_Data.Amount) AS agg FROM Sales_Data WHERE Sales_Data.
Chapter 2 | Supported standards 30 Examples SELECT CHR(67) + SPACE(1) + CHR(70) FROM Salespeople SELECT RTRIM(' ' + Salespeople.Salesperson_ID) AS agg FROM Salespeople SELECT TRIM(SPACE(1) + Salespeople.Salesperson_ID) AS agg FROM Salespeople SELECT LTRIM(' ' + Salespeople.Salesperson_ID) AS agg FROM Salespeople SELECT UPPER(Salespeople.Salesperson) AS agg FROM Salespeople SELECT LOWER(Salespeople.Salesperson) AS agg FROM Salespeople SELECT LEFT(Salespeople.
Chapter 2 | Supported standards Functions that return numbers 31 Description Example EXP Returns a value that is the base of the natural logarithm (e) raised to a power specified by the argument FLOOR Returns the largest integer value that is less than or equal to the argument HOUR Returns the hour part of a value INT Returns the integer part of a number INT(6.
Chapter 2 | Supported standards 32 Functions that return dates Functions that return dates Description Example CURDATE CURRENT_DATE Returns today’s date CURTIME CURRENT_TIME Returns the current time CURTIMESTAMP CURRENT_TIMESTAMP Returns the current timestamp value TIMESTAMPVAL Converts a character string to a timestamp TIMESTAMPVAL('2013-01-30 14:00:00') returns its timestamp value.
Chapter 2 | Supported standards 33 Conditional functions Description Example COALESCE Returns the first value that is not NULL SELECT Salesperson_ID, COALESCE(Sales_Manager, Salesperson) FROM Salespeople NULLIF Compares two values and returns NULL if the two SELECT values are equal; otherwise, returns the first Invoice_ID, value.
Chapter 2 | Supported standards 34 Reserved SQL keywords This section lists reserved keywords that should not be used as names for columns, tables, aliases, or other user-defined objects. If you are getting syntax errors, these errors may be due to using one of these reserved words. If you want to use one of these keywords, you need to use quotation marks to prevent the word from being treated as a keyword.
Chapter 2 | Supported standards 35 EXCEPT IS OPTION EXCEPTION ISOLATION OR EXEC JOIN ORDER EXECUTE KEY OUTER EXISTS LANGUAGE OUTPUT EXTERNAL LAST OVERLAPS EXTRACT LEADING PAD FALSE LEFT PART FETCH LENGTH PARTIAL FIRST LEVEL PERCENT FLOAT LIKE POSITION FOR LOCAL PRECISION FOREIGN LONGVARBINARY PREPARE FOUND LOWER PRESERVE FROM LTRIM PRIMARY FULL MATCH PRIOR GET MAX PRIVILEGES GLOBAL MIN PROCEDURE GO MINUTE PUBLIC GOTO MODULE READ GRANT MONTH REA
Chapter 2 | Supported standards 36 SET USERNAME SIZE USING SMALLINT VALUE SOME VALUES SPACE VARBINARY SQL VARCHAR SQLCODE VARYING SQLERROR VIEW SQLSTATE WHEN STRVAL WHENEVER SUBSTRING WHERE SUM WITH SYSTEM_USER WORK TABLE WRITE TEMPORARY YEAR THEN ZONE TIES TIME TIMESTAMP TIMESTAMPVAL TIMEVAL TIMEZONE_HOUR TIMEZONE_MINUTE TO TODAY TRAILING TRANSACTION TRANSLATE TRANSLATION TRIM TRUE UNION UNIQUE UNKNOWN UPDATE UPPER USAGE USER
Index A ABS function 30 aggregate functions in SQL 28 ALL operator 26 ALTER TABLE (SQL statement) 21 AND operator 27 ANY operator 26 ATAN function 30 ATAN2 function 30 DATEVAL function 32 DAY function 30 DAYNAME function 29 DAYOFWEEK function 30 DEFAULT (SQL clause) 20 DEG function 30 DEGREES function 30 DELETE (SQL statement) 17 DISTINCT operator 8 DROP INDEX (SQL statement) 22 B E BETWEEN operator 25 binary data, use in SELECT 15 blank characters 25 blank value in columns 18 BLOB data type, use in SEL
J JDBC client driver portals 7 Unicode support 7 join 10 K keywords, reserved SQL 34 L LEFT function 29 LEFT OUTER JOIN 10 LENGTH function 31 LIKE operator 25 LN function 31 LOG function 31 logical operators in SQL expressions 27 LOWER function 29 LTRIM function 29 M MAX function 31 MIN function 31 MINUTE function 31 MOD function 30 MONTH function 31 MONTHNAME function 29 N NOT IN operator 25 NOT LIKE operator 25 NOT NULL (SQL clause) 20 NOT operator 27 null value 18 NULLIF function 33 numeric opera
T table aliases 8, 9 TAN function 31 time formats 23 TIME function 29 timestamp formats 23 TIMESTAMPVAL function 32 TIMEVAL function 29 TODAY function 32 TRIM function 29 U Unicode support 7 UNION (SQL operator) 12 UNIQUE (SQL clause) 20 UPDATE (SQL statement) 19 UPPER function 29 USERNAME function 29 V VALUES (SQL clause) 17 W WHERE (SQL clause) 10 WITH TIES (SQL clause) 13 Y YEAR function 31
