Claris FileMaker SQL Reference
© 2013–2020 Claris International Inc. All rights reserved. Claris International Inc. 5201 Patrick Henry Drive Santa Clara, California 95054 FileMaker, FileMaker Cloud, FileMaker Go and the file folder logo are trademarks of Claris International Inc., registered in the U.S. and other countries. Claris, the Claris logo, Claris Connect, and FileMaker WebDirect are trademarks of Claris International Inc. All other trademarks are the property of their respective owners.
Contents Chapter 1 Introduction About this reference About SQL Using a FileMaker Pro 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 statement ALTER TABLE statement CREATE IN
Contents 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 4 28 28 30 32 33 34 35 35 36 37 40
Chapter 1 Introduction As a database developer, you can use Claris™ FileMaker® Pro to create database solutions without any knowledge of SQL. But if you have some knowledge of SQL, you can use a FileMaker Pro 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 the ExecuteSQL function The FileMaker Pro ExecuteSQL function lets you retrieve data from table occurrences named in the relationships graph but independent of any defined relationships. You can retrieve data from multiple tables without creating table joins or any relationship between the tables. In some cases, you may be able to reduce the complexity of your relationships graph by using the ExecuteSQL function.
Chapter 2 Supported standards Use the FileMaker ODBC and JDBC client drivers to access a FileMaker Pro database solution from an ODBC- or JDBC-compliant application. The FileMaker Pro database solution can be hosted by either FileMaker Pro or FileMaker Server. 1 The ODBC client driver supports ODBC 3.0 Level 1. 1 The JDBC client driver provides partial support for the JDBC 3.0 specification.
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 11) 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 Field names can be prefixed with the table name or the table alias. Example Given the table specification FROM employee E, you can refer to the LAST_NAME field as E.LAST_NAME. Table aliases must be used if the SELECT statement joins a table to itself. SELECT * FROM employee E, employee F WHERE E.manager_id = F.employee_id The equal sign (=) includes only matching rows in the results.
Chapter 2 | Supported standards 11 WHERE clause The WHERE clause specifies the conditions that records must meet to be retrieved. The WHERE clause contains conditions in the form: WHERE expr1 rel_operator expr2 expr1 and expr2 can be field names, constant values, or expressions. rel_operator is the relational operator that links the two expressions. Example Retrieve the names of employees who make $20,000 or more.
Chapter 2 | Supported standards 12 HAVING clause The HAVING clause enables you to specify conditions for groups of records (for example, display only the departments that have salaries totaling more than $200,000). It has the following format: HAVING expr1 rel_operator expr2 expr1 and expr2 can be field names, constant values, or expressions. These expressions do not have to match a column expression in the SELECT clause. rel_operator is the relational operator that links the two expressions.
Chapter 2 | Supported standards 13 ORDER BY clause The ORDER BY clause indicates how the records are to be sorted. If your SELECT statement doesn’t include an ORDER BY clause, the records may be returned in any order. The format is: ORDER BY {sort_expression [DESC | ASC]}, ... sort_expression can be the field name or the positional number of the column expression to use. The default is to perform an ascending (ASC) sort. Examples Sort by last_name then by first_name.
Chapter 2 | Supported standards 14 FETCH FIRST format The FETCH FIRST format is: FETCH FIRST [ n [ PERCENT ] ] { ROWS | ROW } {ONLY | WITH TIES } ] n is the number of rows to be returned. The default value is 1 if n is omitted. n is an unsigned integer greater than or equal to 1 unless it is followed by PERCENT. If n is followed by PERCENT, the value may be either a positive fractional value or an unsigned integer. ROWS is the same as ROW. WITH TIES must be used with the ORDER BY clause.
Chapter 2 | Supported standards 15 Examples Using Sample SQL text constant SELECT 'CatDog' FROM Salespeople numeric constant SELECT 999 FROM Salespeople date constant SELECT DATE '2021-06-05' FROM Salespeople time constant SELECT TIME '02:49:03' FROM Salespeople timestamp constant SELECT TIMESTAMP '2021-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 DISTINCT Amo
Chapter 2 | Supported standards 16 Retrieving the contents of a container field: CAST() function and GetAs() function You can retrieve file reference information, binary data, or data of a specific file type from a container field. 1 To retrieve file reference information from a container field, such as the file path to a file, picture, or QuickTime movie, use the CAST() function with a SELECT statement.
Chapter 2 | Supported standards File type Description 'GIFf' Graphics Interchange Format 'JPEG' Photographic images 'TIFF' Raster file format for digital images 'PDF ' Portable Document Format 'PNGf' Bitmap image format 17 DELETE statement Use the DELETE statement to delete records from a database table. The format of the DELETE statement is: DELETE FROM table_name [ WHERE { conditions } ] Note The WHERE clause determines which records are to be deleted.
Chapter 2 | Supported standards 18 Example Insert a list of expressions. INSERT INTO emp (last_name, first_name, emp_id, salary, hire_date) VALUES ('Smith', 'John', 'E22345', 27500, DATE '2019-06-05') Each INSERT statement adds one record to the database table. In this case a record has been added to the employee database table, emp. Values are specified for five columns. The remaining columns in the table are assigned a blank value, meaning Null.
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 Defining a column to be UNIQUE automatically selects the Unique Validation Option for the corresponding field in the FileMaker Pro database file. 1 Defining a column to be NOT NULL automatically selects the Not Empty Validation Option for the corresponding field in the FileMaker Pro database file. The field is flagged as a Required Value in the Fields tab of the Manage Database dialog box in FileMaker Pro.
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 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.column_name DROP INDEX ON table_name (column_name) Remove an index when your database file is too large, or you don’t often use a field in queries.
Chapter 2 | Supported standards 24 For ODBC and JDBC applications, FileMaker software accepts the ODBC/JDBC format date, time, and timestamp constants in braces ({}). Examples 1 {D '2019-06-05'} 1 {T '14:35:10'} 1 {TS '2019-06-05 14:35:10'} FileMaker software allows the type specifier (D, T, TS) to be in upper case or lower case. You may use any number of spaces after the type specifier, or even omit the space. FileMaker software also accepts SQL-92 syntax ISO date and time formats with no braces.
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. In the following, last_name is 'JONES ' and first_name is 'ROBERT '.
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 Example SELECT Sales_Data.Invoice_ID FROM Sales_Data WHERE Sales_Data.Salesperson_ID = 'SP-1' SELECT Sales_Data.Amount FROM Sales_Data WHERE Sales_Data.Invoice_ID <> 125 SELECT Sales_Data.Amount FROM Sales_Data WHERE Sales_Data.Amount > 3000 SELECT Sales_Data.Time_Sold FROM Sales_Data WHERE Sales_Data.Time_Sold < '12:00:00' SELECT Sales_Data.Company_Name FROM Sales_Data WHERE Sales_Data.Company_Name LIKE '%University' SELECT 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 Example COUNT (DISTINCT last_name) In this example, only unique last name values are counted. Aggregate function Returns SUM The total of the values in a numeric field expression. For example, SUM(SALARY) returns the sum of all salary field values. AVG The average of the values in a numeric field expression. For example, AVG(SALARY) returns the average of all salary field values. COUNT The number of values in any field expression.
Chapter 2 | Supported standards 30 Functions that return character strings Functions that return character strings Description Example CHR Converts an ASCII code to a one-character string CHR(67) returns C CURRENT_USER Returns the login ID specified at connect time DAYNAME Returns the name of the day that corresponds to a specified date RTRIM Removes trailing blanks from a string RTRIM(' TRIM Removes leading and trailing blanks from a string TRIM(' ABC ') returns 'ABC' LTRIM Removes leadi
Chapter 2 | Supported standards Example 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 32 Functions that return numbers Functions that return numbers Description ABS Returns the absolute value of the numeric expression ATAN Returns the arc tangent of the argument as an angle expressed in radians ATAN2 Returns the arc tangent of x and y coordinates as an angle expressed in radians CEIL CEILING Returns the smallest integer value that is greater than or equal to the argument DEG DEGREES Returns the number of degrees of the argument, which is an angle
Chapter 2 | Supported standards 33 Functions that return numbers Description SIGN An indicator of the sign of the argument: -1 for negative, 0 for 0, and 1 for positive SIN Returns the sine of the argument SQRT Returns the square root of the argument TAN Returns the tangent of the argument YEAR Returns the year part of a date Example YEAR(DATE '2019-01-30') returns 2019 Functions that return dates Functions that return dates Description Example CURDATE CURRENT_DATE Returns today’s date
Chapter 2 | Supported standards 34 Conditional functions Conditional functions Description CASE WHEN Simple CASE format Example SELECT Invoice_ID, Compares the value of input_exp to the values CASE Company_Name of value_exp arguments to determine the result. WHEN 'Exports UK' THEN 'Exports UK Found' CASE input_exp WHEN 'Home Furniture Suppliers' THEN 'Home Furniture {WHEN value_exp THEN result...
Chapter 2 | Supported standards 35 FileMaker system objects FileMaker Pro database files include the following system objects that you can access using SQL queries. FileMaker system tables Every FileMaker Pro database file includes two system tables: FileMaker_Tables and FileMaker_Fields. For ODBC applications, these tables are included in the information returned by the catalog function SQLTables.
Chapter 2 | Supported standards 36 FileMaker system columns FileMaker software adds system columns (fields) to all of the rows (records) in all of the tables that are defined in the FileMaker Pro file. For ODBC applications, these columns are included in the information returned by the catalog function SQLSpecialColumns. For JDBC applications, these columns are included in the information returned by the DatabaseMetaData method getVersionColumns. The columns can also be used in ExecuteSQL functions.
Chapter 2 | Supported standards 37 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. Example Use the DEC keyword as a data element name.
Chapter 2 | Supported standards 38 END_EXEC INTEGER OF ESCAPE INTERSECT OFFSET EVERY INTERVAL ON EXCEPT INTO ONLY EXCEPTION IS OPEN EXEC ISOLATION OPTION EXECUTE JOIN OR EXISTS KEY ORDER EXTERNAL LANGUAGE OUTER EXTRACT LAST OUTPUT FALSE LEADING OVERLAPS FETCH LEFT PAD FIRST LENGTH PART FLOAT LEVEL PARTIAL FOR LIKE PERCENT FOREIGN LOCAL POSITION FOUND LONGVARBINARY PRECISION FROM LOWER PREPARE FULL LTRIM PRESERVE GET MATCH PRIMARY GLOBAL MAX PRI
Chapter 2 | Supported standards 39 SCROLL UNION SECOND UNIQUE SECTION UNKNOWN SELECT UPDATE SESSION UPPER SESSION_USER USAGE 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 TRANS
Index A ABS function 32 aggregate functions in SQL 28 ALL operator 26 ALTER TABLE (SQL statement) 22 AND operator 27 ANY operator 26 ATAN function 32 ATAN2 function 32 DATEVAL function 33 DAY function 32 DAYNAME function 30 DAYOFWEEK function 32 DEFAULT (SQL clause) 20 DEG function 32 DEGREES function 32 DELETE (SQL statement) 17 DISTINCT operator 8 DROP INDEX (SQL statement) 23 B E BaseFileName 35 BaseTableName 35 BETWEEN operator 26 binary data, use in SELECT 15 blank characters 25 blank value in colu
Index IS NOT NULL operator 26 IS NULL operator 26 J JDBC client driver portals 7 Unicode support 7 join 10 K keywords, reserved SQL 37 L LEFT function 30 LEFT OUTER JOIN 10 LENGTH function 32 LIKE operator 26 LN function 32 LOG function 32 logical operators in SQL expressions 27 LOWER function 30 LTRIM function 30 M MAX function 32 MIN function 32 MINUTE function 32 MOD function 32 ModCount 35 MONTH function 32 MONTHNAME function 30 N NOT IN operator 26 NOT LIKE operator 26 NOT NULL (SQL clause) 21 NO
Index SQRT function 33 standards compliance 7 string functions 30 STRVAL function 30 subqueries 17 SUBSTR function 30 SUBSTRING function 30 syntax errors 37 system tables 35 T table aliases 8, 9 TableId 35 TableName 35 TAN function 33 time formats 24 TIME function 30 timestamp formats 24 TIMESTAMPVAL function 33 TIMEVAL function 30 TODAY function 33 TRIM function 30 TRUNCATE TABLE (SQL statement) 21 U Unicode support 7 UNION (SQL operator) 12 UNIQUE (SQL clause) 21 UPDATE (SQL statement) 19 UPPER functio
