|
|
|
SELECT
Description
Selects the specified column values from one or more rows contained in the table(s) specified in the FROM clause. The selection of rows is restricted by the WHERE clause. The temporary table derived through the clauses of a select statement is called a result table.
The format of the SELECT statement is a query expression with optional ORDER BY and FOR UPDATE clauses. For more detail on query expressions, see Query Expressions.
Syntax
select_statement ::
query_expression
ORDER BY { expr | posn } [ ASC | DESC ]
[ , { expr | posn } [ASC | DESC] ,... ]
FOR UPDATE [ OF [table].column_name, ... ] [ NOWAIT ];
query_expression ::
query_specification
| query_expression set_operator query_expression
| ( query_expression )
set_operator ::
{ UNION [ ALL ] | INTERSECT | MINUS }
query_specification ::
SELECT [ ALL | DISTINCT ] [ SKIP N ] [ TOP N ]
{
*
| { table_name | alias } . * [, { table_name | alias } . * ] ...
| { { { expr | NULL } [ [ AS ] [ ' ] column_title [ ' ] ] }
|
{ [ [ ' ] column_title [ '] = ] { expr | NULL } }
}
[,
{ { { expr | NULL } [ [ AS ] [ ' ] column_title [ ' ] ] }
|
{ [ [ ' ] column_title [ '] = ] { expr | NULL } }
}
] ...
}
} FROM table_ref [ { ctree ORDERED } ] [ , table_ref [ { ctree ORDERED } ] ...
[ WHERE search_condition ]
[ GROUP BY [table.]column_name
[, [table.]column_name ] ...
[ HAVING search_condition ]
table_ref ::
table_name [ AS ] [ alias [ ( column_alias [ , ... ] ) ] ]
| ( query_expression ) [ AS ] alias [ ( column_alias [ , ... ] ) ]
| [ ( ] joined_table [ ) ]
| procedure_name(proc_arg [,...]) [ AS ] [ alias ]
joined_table ::
table_ref CROSS JOIN table_ref
| table_ref [ INNER | LEFT [ OUTER ] ] JOIN table_ref ON search_condition
Arguments
query_expression
See Query Expressions.
expr
See Expressions
expr can also be a scalar sub-query.
See Scalar Sub-query Expressions
ORDER BY clause
See ORDER BY CLAUSE
FOR UPDATE clause
NOWAIT
c-tree supports non-blocking locks with an info type of UPDATE_NOWAIT_SUPPORTED, which returns TRUE or FALSE based on whether the storage system supports NOWAIT or not. If the storage system supports NOWAIT, then SELECT .. FOR UPDATE statements use a new fetch hint TPL_FH_WRITE_WITH_NOWAIT instead of TPL_FH_WRITE. If the row is already locked, instead of waiting (as the old logic would have done), the logic returns the ELCK_UPDATE_NOWAIT error and execution of the statement stops.
Note that the code does not return ELCK_UPDATE_NOWAIT; it returns c-tree error 42 mapped into -17042. The code does not have any check on ELCK_UPDATE_NOWAIT.
procedure_name
A procedure used in a table_ref must return a result_set and may not have any OUT or IN/OUT parameters. That is, only IN parameters are supported.
Note: Use of ODER BY in a FOR UPDATE clause is a non-standard SQL feature as specified by SQL92. Also, full cursor update is not supported by FairCom DB SQL. See UPDATE.
Authorization
The user executing this statement must have any of the following privileges:
SQL Compliance |
SQL-92. Extensions: FOR UPDATE clause. ODBC Extended SQL grammar. |
Environment |
Embedded SQL (within DECLARE), interactive SQL, ODBC applications |
Related Statements |
Query Expressions, DECLARE CURSOR, OPEN, FETCH, CLOSE |
