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 Statement—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".

However,

is incorrect: the result column is "person", but the sort clause incorrectly specifies the base table column "ename".