FileMaker 15 ® SQL Reference
© 2013-2016 FileMaker, Inc. All Rights Reserved. FileMaker, Inc. 5201 Patrick Henry Drive Santa Clara, California 95054 FileMaker and FileMaker Go are trademarks of FileMaker, Inc. registered in the U.S. and other countries. The file folder logo and FileMaker WebDirect 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 FileMaker 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 TRUNCATE TABLE state
SQL functions Aggregate functions Functions that return character strings Functions that return numbers Functions that return dates Conditional functions FileMaker system objects FileMaker system tables FileMaker system columns Reserved SQL keywords Index 29 29 30 31 33 33 34 34 35 36 39
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 The scope of the GROUP BY clause is the table expression in the FROM clause. As a result, the column expressions specified by columns must be from the tables specified in the FROM clause.
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 '2016-06-05' FROM Salespeople time constant SELECT TIME '02:49:03' FROM Salespeople timestamp constant SELECT TIMESTAMP '2016-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 ALTER TABLE statement Use the ALTER TABLE statement to change the structure of an existing table in a database file. You can modify only one column in each statement.
Chapter 2 | Supported standards 23 FileMaker automatically creates indexes as needed. Using CREATE INDEX causes the index to be built immediately rather than on demand. Example CREATE INDEX ON Salespeople.Salesperson_ID DROP INDEX statement Use the DROP INDEX statement to remove an index from a database file. The format of the DROP INDEX statement is: DROP INDEX ON table_name.
Chapter 2 | Supported standards 24 Constants Constants are values that do not change. For example, in the expression PRICE * 1.05, the value 1.05 is a constant. Or you might assign a value of 30 to the constant Number_Of_Days_In_June. You must enclose character constants in pairs of single quotation marks ('). To include a single quotation mark in a character constant enclosed by single quotation marks, use two single quotation marks together (for example, 'Don''t').
Chapter 2 | Supported standards 25 Exponential/scientific notation Numbers can be expressed using scientific notation. Example SELECT column1 / 3.4E+7 FROM table1 WHERE calc < 3.4E-6 * column2 Numeric operators You can include the following operators in number expressions: +, -, *, /, and ^ or ** (exponentiation). You can precede numeric expressions with a unary plus (+) or minus (-). Character operators You can concatenate characters.
Chapter 2 | Supported standards 26 Relational operators Operator Meaning = Equal <> Not equal > Greater than >= Greater than or equal to < Less than <= Less than or equal to LIKE Matching a pattern NOT LIKE Not matching a pattern IS NULL Equal to Null IS NOT NULL Not equal to Null BETWEEN Range of values between a lower and upper bound IN A member of a set of specified values or a member of a subquery NOT IN Not a member of a set of specified values or a member of a subquery EX
Chapter 2 | Supported standards 27 SELECT Sales_Data.Invoice_ID FROM Sales_Data WHERE Sales_Data.Invoice_ID BETWEEN 1 AND 10 SELECT COUNT(Sales_Data.Invoice_ID) AS agg FROM Sales_Data WHERE Sales_Data.INVOICE_ID IN (50,250,100) SELECT COUNT(Sales_Data.Invoice_ID) AS agg FROM Sales_Data WHERE Sales_Data.INVOICE_ID NOT IN (50,250,100) SELECT COUNT(Sales_Data.Invoice_ID) AS agg FROM Sales_Data WHERE Sales_Data.INVOICE_ID NOT IN (SELECT Sales_Data.Invoice_ID FROM Sales_Data WHERE Sales_Data.
Chapter 2 | Supported standards 28 Operator precedence As expressions become more complex, the order in which the expressions are evaluated becomes important. This table shows the order in which the operators are evaluated. The operators in the first line are evaluated first, and so on. Operators in the same line are evaluated left to right in the expression.
Chapter 2 | Supported standards 29 SQL functions FileMaker SQL supports many functions you can use in expressions. Some of the functions return characters strings, some return numbers, some return dates, and some return values that depend on conditions met by the function arguments. Aggregate functions Aggregate functions return a single value from a set of records.
Chapter 2 | Supported standards 30 You cannot use an aggregate function as an argument to other functions. If you do, FileMaker returns the error code 8309 (“Expressions involving aggregations are not supported”). For example, the following statement is not valid because the aggregate function SUM cannot be used as an argument to the function ROUND: SELECT ROUND(SUM(Salary), 0) FROM Payroll However, aggregate functions can use functions that return numbers as arguments.
Chapter 2 | Supported standards 31 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 32 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 33 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('2016-01-30 14:00:00') returns its timestamp value DATE TODAY Returns today’s date If today is 11/21/2016, DATE() returns 2016-11-21 DATEVAL Converts a charact
Chapter 2 | Supported standards 34 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 35 1 FieldType - The SQL data type of the field. 1 FieldId - The unique ID for the field. 1 FieldClass - One of three values: Summary, for summary fields; Calculated, for calculated results; or Normal. 1 FieldReps - The number of repetitions of the field. 1 ModCount - The total number of times changes to this table’s definition have been committed.
Chapter 2 | Supported standards 36 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 37 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 38 SET USER SIZE USERNAME SMALLINT USING SOME VALUE SPACE VALUES SQL VARBINARY SQLCODE VARCHAR SQLERROR VARYING SQLSTATE VIEW STRVAL WHEN SUBSTRING WHENEVER SUM WHERE SYSTEM_USER WITH TABLE WORK TEMPORARY WRITE THEN YEAR TIES ZONE TIME TIMESTAMP TIMESTAMPVAL TIMEVAL TIMEZONE_HOUR TIMEZONE_MINUTE TO TODAY TRAILING TRANSACTION TRANSLATE TRANSLATION TRIM TRUE TRUNCATE UNION UNIQUE UNKNOWN UPDATE UPPER USAGE
Index A D ABS function 31 aggregate functions in SQL 29 ALL operator 26 ALTER TABLE (SQL statement) 22 AND operator 27 ANY operator 26 ATAN function 31 ATAN2 function 31 date formats 24 DATE function 33 date operators in SQL expressions 25 DATEVAL function 33 DAY function 31 DAYNAME function 30 DAYOFWEEK function 31 DEFAULT (SQL clause) 20 DEG function 31 DEGREES function 31 DELETE (SQL statement) 17 DISTINCT operator 8 documentation 5 DROP INDEX (SQL statement) 23 B BaseFileName 34 BaseTableName 34 BET
H O HAVING (SQL clause) 11 HOUR function 32 ODBC client driver portals 7 Unicode support 7 ODBC standards compliance 7 OFFSET (SQL clause) 13 online documentation 5 operator precedence in SQL expressions 28 OR operator 27 ORDER BY (SQL clause) 12 OUTER JOIN 10 I IN operator 26 INNER JOIN 10 INSERT (SQL statement) 17 INT function 32 IS NOT NULL operator 26 IS NULL operator 26 J JDBC client driver portals 7 Unicode support 7 join 10 K keywords, reserved SQL 36 L LEFT function 30 LEFT OUTER JOIN 10
SQL standards compliance 7 SQL statements ALTER TABLE 22 CREATE INDEX 22 CREATE TABLE 20 DELETE 17 DROP INDEX 23 INSERT 17 reserved keywords 36 SELECT 8 supported by client drivers 7 TRUNCATE TABLE 21 UPDATE 19 SQL_C_WCHAR data type 7 SQL-92 7 SQRT function 32 standards compliance 7 string functions 30 STRVAL function 30 subqueries 18 SUBSTR function 30 SUBSTRING function 30 syntax errors 36 system tables 34 T table aliases 8, 9 TableId 34 TableName 34 TAN function 32 time formats 24 TIME function 30 t
