SQL Reference
Table Of Contents
- Chapter 1 Introduction
- 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 INDEX statement
- DROP INDEX statement
- SQL expressions
- SQL functions
- FileMaker system objects
- Reserved SQL keywords
- Index
Chapter 2 | Supported standards 34
FileMaker system objects
FileMaker database files include the following system objects that you can access using SQL
queries.
FileMaker system tables
Every FileMaker 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. For JDBC applications, these tables are included in the
information returned by the DatabaseMetaData method getTables. The tables can also be used
in ExecuteSQL functions.
FileMaker_Tables
The FileMaker_Tables table contains information about the database tables defined in the
FileMaker file.
The FileMaker_Tables table includes a row for each table occurrence in the relationships graph
with the following columns:
1 TableName - The name of the table occurrence.
1 TableId - The unique ID for the table occurrence.
1 BaseTableName - The name of the base table from which the table occurrence was created.
1 BaseFileName - The FileMaker filename for the database file that contains the base table.
1 ModCount - The total number of times changes to this table’s definition have been committed.
Example
SELECT TableName FROM FileMaker_Tables WHERE TableName LIKE ’Sales%’
FileMaker_Fields table
The FileMaker_Fields table contains information about the fields defined in the FileMaker file.
The FileMaker_Fields table includes the following columns:
1 TableName - The name of the table that contains the field.
1 FieldName - The name of the field.
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
values are equal; otherwise, returns the first
value.
SELECT
Invoice_ID,
NULLIF(Amount, -1),
Salesperson_ID
FROM
Sales_Data
Conditional
functions Description Example