SQL Statements › SELECT › Select-Statement › Description

Description

ORDER BY clause

You can specify the order for rows in the result table. For information on using the ORDER BY clause, see ORDER BY Clause.

FOR UPDATE OF c-name

This CA Datacom/DB extension is provided for compatibility with other SQL implementations. The functionality of update indication is actually accomplished by other means in CA Datacom/DB. The column(s) must belong to the table or view named in the FROM clause of the SELECT statement. Column names (c-name in the diagram) must be separated by commas.

OPTIMIZE FOR n ROWS

This CA Datacom/DB extension tells the optimizer how many rows are normally fetched before the cursor is closed. This is useful when there is a choice between a key that would build a temporary table and give a lower cost if all rows are fetched, and a key that does not build a temporary table and would take longer if all rows are fetched, but takes less time when only a few rows are fetched.

Note: The number n is not a limit (as it is, for example, in FOR FIRST n). The complete set of rows can be fetched. The number n in this clause is merely input to the optimizer.

FOR READ/FETCH ONLY

This clause is provided for compatibility with other SQL implementations, but if you use it and then try to do an update or delete on the current cursor, you receive an SQL return code -130 CURSOR NOT UPDATABLE (SQLSTATE 24S05).

FETCH FIRST n ROW/ROWS ONLY

The FETCH FIRST clause allows you to control how many rows are retrieved by a query, giving you the ability to limit the number of fetches that are done. FETCH FIRST can therefore be used to prevent runaway queries.

Note: Queries that require the resulting set to be sorted have to perform the sort on the entire resulting set. For example, if a non-indexed column is being ordered by the use of an ORDER BY clause, to return the correct n rows the entire resulting set would need to be generated and sorted. In this case, the savings would be minimal even though CA Datacom/DB would prevent an excessive number of FETCHes from occurring.

The n can be optionally used to specify the number of rows to be fetched. When a specified number of rows have been fetched, a SQL return code number +100 NO ROW FOUND (SQLSTATE 02000) is received. If n is not specified, the resulting set is limited to one row.

Either ROW or ROWS must be specified.

ONLY must be specified. ONLY is provided for compatibility with other SQL implementations.