SQL Statements › SELECT › Select-Statement › Description

Description

ORDER BY clause

You can specify the order for rows in the result table. For more information, 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 accomplished by other means in CA Datacom/DB. The columns must belong to the table or view that is named in the FROM clause of the SELECT statement. Commas must separate the column names (c-name in the diagram).

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 builds a temporary table and gives a lower cost if all rows are fetched, and
• A key that does not build a temporary table and takes 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. 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).

NOTE: FOR FETCH ONLY does not force locking or have any effect on locking in any way.

FETCH FIRST n ROW/ROWS ONLY

The FETCH FIRST clause allows you to control how many rows a query retrieves. This clause allows you 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.

QUERYNO nnnnn

Use this optional clause to identify a query in various reports. The supplied value is stored in the Source Cache Entry Dynamic System Table SC_ENTRY. If this option is not used, a default unique negative value is used in SC_ENTRY.

Valid values are any positive integer.

Note: CA Datacom does not check for a unique value, but because the intention is to uniquely identify the query, we recommend that you use a unique value.