ODBC and JDBC Developer’s Guide

34 FileMaker ODBC and JDBC Developer’s Guide
When using the UNION operator, the select lists for each SELECT statement must have the same number
of column expressions, with the same data types, and must be specified in the same order. For example:
SELECT last_name, salary, hire_date FROM emp UNION SELECT name, pay,
birth_date FROM person
This example has the same number of column expressions, and each column expression, in order, has the
same data type.
The following example is not valid because the data types of the column expressions are different (SALARY
from EMP has a different data type than LAST_NAME from RAISES). This example has the same number
of column expressions in each SELECT statement, but the expressions are not in the same order by data
type.
SELECT last_name, salary FROM emp UNION SELECT salary, last_name FROM
raises
ORDER BY clause
The ORDER BY clause indicates how the records are to be sorted. The format is:
ORDER BY {sort_expression [DESC | ASC]}, ...
sort_expression can be field names, expressions, or the positional number of the column expression to
use. The default is to perform an ascending (ASC) sort.
For example, to sort by last_name then by first_name, you could use either of the following SELECT
statements:
SELECT emp_id, last_name, first_name FROM emp ORDER BY last_name,
first_name
or
SELECT emp_id, last_name, first_name FROM emp ORDER BY 2,3
In the second example, last_name is the second column expression following SELECT, so ORDER BY 2
sorts by
last_name.
FOR UPDATE clause
The FOR UPDATE clause locks the records of the database table selected by the SELECT statement. The
format is:
FOR UPDATE [OF column_expressions]
column_expressions is a list of field names in the database table that you intend to update, separated by
a comma.
column_expressions is optional.
The following example returns all records in the employee database that have a SALARY field value of
more than $20,000. When each record is fetched, it is locked. If the record is updated or deleted, the lock is
held until you commit the change. Otherwise, the lock is released when you fetch the next record.
SELECT * FROM emp WHERE salary > 20000 FOR UPDATE OF last_name, first_name,
salary