ODBC and JDBC Developer’s Guide

Chapter 4
|
Supported standards 27
The following example uses the ORDER BY clause to sort by both last name and first name in ascending
order:
SELECT emp_id, last_name, first_name FROM emp ORDER BY last_name,
first_name
Additional examples:
Notes from the examples
A constant is a single, literal value you can include in a SELECT statement, whereas a column is a
reference to a field in the FileMaker database file (the field can contain many distinct values).
The asterisk (*) wildcard character is shorthand for “everything”. For the example SELECT * FROM
Salespeople, the result is all the rows in the Salespeople table. For the example SELECT DISTINCT
* FROM Salespeople, the result is all the unique rows in the Salespeople table (no duplicates).
Note SELECT * statements on larger databases might not function correctly. To avoid potential confusion,
avoid wildcards and specify table and column names (without aliases).
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.
To retrieve binary data, use a standard SELECT statement. For example:
SELECT Company_Brochures FROM Sales_Data
Using Sample SQL
text constant
SELECT 'CatDog' FROM Salespeople
numeric constant
SELECT 999 FROM Salespeople
date constant
SELECT DATE '2004-06-05' FROM Salespeople
time constant
SELECT TIME '02:49:03' FROM Salespeople
timestamp constant
SELECT TIMESTAMP '2004-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 Amount FROM Sales_Data
date column
SELECT Date_Sold FROM Sales_Data
SELECT DISTINCT Date_Sold FROM Sales_Data
time column
SELECT Time_Sold FROM Sales_Data
SELECT DISTINCT Time_Sold FROM Sales_Data
timestamp column
SELECT Timestamp_Sold FROM Sales_Data
SELECT DISTINCT Timestamp_Sold FROM Sales_Data
BLOB
a
column
a. A BLOB is a FileMaker database file container field.
SELECT Company_Brochures FROM Sales_Data
SELECT GETAS(Company_Logo, 'JPEG') FROM Sales_Data
Wildcard *
SELECT * FROM Salespeople
SELECT DISTINCT * FROM Salespeople