RETRIEVE--Retrieve Table Rows
Valid in: QUEL, EQUEL
Retrieve rows from a table.
This statement has the following format:
Interactive QUEL syntax:
retrieve [[into] tablename] [unique]
(target_list) [where qual]
[sort [by] columnname [:sortorder] {, columnname [:sortorder]}]
[order [by] columnname [:sortorder] {,columnname[:sortorder]}]
[with with_clause]
The with clause is valid only when retrieving into a table. The with clause consists of the keyword with followed by a comma-separated list of one or more of the following options:
structure=storage structure name
key=(column list)
[no]journaling
[no]duplicates
location=locationame
fillfactor=1...100%
minpages=(>0)
maxpages=(>0)
nonleaffill=1...100%
leaffill/indexfill=1...100%
maxindexfill=ignored
allocation=(>0) (only for retrieve into)
extend=(>0) (only for retrieve into)
Embedded QUEL syntax, to retrieve into host variables:
## [repeat] retrieve [unique] (variable=result_expression
## {,variable = result_expression})
## [where qual] [sort [by] result_column {, result_column}]
## [{
## program code
## }]
The retrieve statement fetches all rows that satisfy the criteria specified in the where clause, and optionally stores the rows in a new table. To retrieve all columns from a table, specify tablename.all.
If you are using interactive QUEL, you can display the results; if you are using embedded QUEL (EQUEL), you can store the resulting rows in host language variables, enabling your application program to process the rows.
To store the results of the retrieve in a new table, specify into tablename. (In interactive QUEL, if you do not specify into tablename, the result is displayed.) A table with this name (owned by the user) must not already exist. The current user is the owner of the new table.
The new table's column names are specified in the target_list result column names. If the source column has a simple default defined, the result column inherits the default. For details about column defaults, see the create table statement description in the SQL Reference Guide.
The default structure for
tablename is cheap (compressed heap); if sort by is specified, the default structure is cheapsort. You can override the default structure using the set ret_into statement, described in this chapter, or the with structure clause. You can specify the characteristics of the new table using the optional with clause. For details about these parameters, see
MODIFY--Change Table or Index Properties.
Locationname specifies the location where the table is to be created. The location must exist, and the database must have been extended to the corresponding area. If no location is specified, the default location for the database is assumed.
To remove duplicate rows from the result, specify the keyword unique. If you specify unique, rows are sorted on all the columns in the target_list (beginning with the first column) and duplicate rows are removed from the result.
To sort a table without removing duplicate rows, specify order by. To sort and remove duplicate rows, specify sort by. (Order by and sort by are mutually exclusive options.
Retrieve unique with an order by clause is functionally equivalent to retrieve with a sort by clause.) By default, rows are sorted in ascending order. You can override this default by specifying a sortorder of descending (or d) in the sort by or order by clause.
When you use the sort by or order by clause, you must specify the column name that appears in the result table. For example, the following two retrieve statements produce the same results: the first one sorts by base table column "ename", and the second assigns "ename" to the result column "person" and sorts by "person".
retrieve (e.ename, e.dept) sort by ename
retrieve (person=e.ename, e.dept) sort by person
However,
retrieve (person=e.ename, e.dept) sort by ename
is incorrect: the result column is "person", but the sort clause incorrectly specifies the base table column "ename".
Last modified date: 08/28/2024