SQL SELECT Statement

PURPOSE: This statement identifies a group of qualifying rows from one or more tables. After an SQL SELECT statement has been executed, you can use the SQL FETCH statement to retrieve one row at a time from the group of rows that were selected.

Syntax

SQL [FOR CURSOR cursor_name] SELECT
   [ROWID] select_list
   [DISTINCT] select_list
   FROM \\table_name\\
   [WHERE str_exp]
   [ORDER BY str_exp]
   [HUSH]

FOR CURSOR cursor_name	specifies which cursor if multiple cursors are to be used.
ROWID	specifies ROWID in the SQL SELECT column list. It must be the first column specified. It will cause ACCENT R to store the ROWID in the system field @ROWID when the user does a FETCH for that group of selected rows.
DISTINCT	includes only unique rows in the results. Only rows with unique values in the select_list are considered.
select_list	specifies the column names to be retrieved. The list is composed of one or more column names (separated by commas) or an asterisk (‘’) that represents all columns in the order that they were specified when the table was created. The asterisk may be qualified by a table name (Oracle extension). For example, BOOKS_ORA. would select all columns from the table that was related with the designator "BOOKS_ORA".
FROM table_name	is the designator used in the RELATE statement for the table. This is the name of the table or table names from which rows may be selected. This statement makes a shared lock on a specified table more restrictive by holding it until the completion of the transaction that is defined by the SQL COMMIT TRANSACTION and SQL ROLLBACK TRANSACTION statements. Normally the lock is released as soon as the required table or data page is no longer needed, whether or not the transaction has been completed. This option only applies to the table for which it was specified and only for the duration of the transaction.
WHERE str_exp	is a quoted literal, a character or an alphabetic field, a function, or a concatenation of any of the preceding that evaluates to a string that sets the search condition for the rows that are retrieved. You can set the string to contain any valid WHERE clause including column names, expressions, arithmetic operators, comparison operators, the keywords NOT, LIKE, IS NULL, AND, OR, BETWEEN, IN, EXISTS, ANY, ALL, sub-queries, or any combination of these items. This string can be built dynamically from within the Process Module. There is no limit on the number of search conditions that can be included. This search condition cannot contain aggregates. See the 'WHERE clause' in the Oracle SQL Language Reference Manual for details.
ORDER BY str_exp	is a quoted literal, a character or an alphabetic field, a function, or a concatenation of any of the preceding that evaluates to a string that specifies how to sort the results by columns. You can set the string to contain any valid ORDER BY clause including a column name, an expression, or a number representing the position of the item in the select_list. You can ORDER BY columns that do not appear in the select_list. If you sort by a select_list number, the columns to which the ORDER BY refers must be included in the select list and the select_list cannot be "". The sort list may be terminated with "ASC" or "DESC". "ASC" sorts results in ascending order, which is the default. "DESC" sorts the results in descending order. See the 'ORDER BY* clause' in the Oracle SQL Language Reference Manual for details.
HUSH	shuts off warning, error, auxiliary, and SQL messages for the execution of that statement only. The text of the error message can be found in the system function @ERROR_MESSAGE.

Example

SQL SELECT * FROM BOOKS_ORA, ROYALTIES_ORA WHERE &
"BOOKS_ORA.TITLE_CODE =ROYALTIES_ORA.TITLE_CODE" ORDER BY &
"BOOKS_ORA.TITLE_CODE"

NOTES: A row selected by a SELECT statement will not be available after the next SELECT statement executes. The system field @SQLCODE contains the return status of the SQL SELECT statement.

The ORDER BY clause renders the transaction READ ONLY for Oracle.

Example using ROWID

1. The select sets up to get ROWID stored into @ROWID.

SQL SELECT ROWID, FI, FN, FC FROM ORA

2. The FETCH will cause the ROWID of the current row to be stored into @ROWID.

SQL FETCH NEXT ROW

TYPE @ROWID

3. Now UPDATE current row is possible because @ROWID is set for the current row that is to be updated.

SQL UPDATE ORA CURRENT ROW

NOTES: When ROWID is specified it can be used in a WHERE clause to update a specific row. You can save it after the FETCH and do the update later without doing a SELECT and FETCH if using multiple cursors.

Since there is only one @ROWID care must be given to the SQL UPDATE to ensure @ROWID contains the correct value for the ROW being updated.

SEE ALSO: OSQL SELECT command.