SELECT

Retrieves data from the tables in the database.

 

 

Usage

Embedded, Interactive, Module, ODBC, Procedural, JDBC.

In ESQL, the SELECT statement may only be used to declare a cursor or as input to a PREPARE statement.

In a procedural usage context, the SELECT statement may only be used to declare a cursor.

In interactive SQL, the SELECT statement is used for interactive data retrieval. See the Mimer SQL User's Manual, Retrieving Data for more details.

Description

SELECT Statements

Simple SELECT statements are built from a select-expression, see The SELECT Expression, optionally followed by a FOR UPDATE OF clause.

SELECT statements are used in embedded SQL (including procedural usage contexts) to define cursors and as the input to dynamic PREPARE statements.

The embedded SELECT statement is syntactically equivalent to the interactive data retrieval SELECT statement. In embedded contexts however, the statement cannot be used to retrieve data directly but must be implemented through a cursor.

The FOR UPDATE Clause

If the SELECT statement defines a cursor intended for UPDATE CURRENT statements, the for-update clause must be specified. If the FOR UPDATE OF version is used, it must include all the columns to be updated.

Each column specified in the for-update-of must belong to the table or view named in the from clause of the SELECT statement, although the columns in FOR UPDATE OF do not need to be specified in the select clause. No column may be named more than once in the for-update-of clause.

Column names in the for-update-of clause may not be qualified by the name of the table or view. They are implicitly qualified by the table reference in the from clause of the select specification.

FOR UPDATE OF may not be specified if the statement defines a read-only result set, see Updatable Result Sets.

Updatable Result Sets

A result set is only updatable if all of the following conditions are true (otherwise the result set is read-only):

•the keyword DISTINCT is not specified

•there are no set-functions in the SELECT list (AVG, COUNT, MAX, MIN, SUM)

•the FROM clause specifies exactly one table reference and that table reference refers either to a base table or an updatable view

•the result set is not the product of an explicit INNER or OUTER JOIN

•the GROUP BY clause is not included

•the HAVING clause is not included

•the keyword EXCEPT is not included

•the keyword INTERSECT is not included

•the keyword UNION is not included

•the ORDER BY clause is not included

•it is not the result of a call to a result set procedure

•The FOR UPDATE clause has been specified.

A cursor which addresses a read-only result table may not be used for DELETE CURRENT or UPDATE CURRENT statements.

The FOR READ ONLY Clause

The FOR READ ONLY clause is optional since SELECT statements by default are read-only.

Examples

SELECT format, category_id

FROM formats

ORDER BY LOWER(format), category_id;

 

SELECT format AS format_name, category

FROM formats

ORDER BY CASE category WHEN 'ROCK' THEN 1

                       WHEN 'JAZZ' THEN 2

                       ELSE 3

         END

OFFSET 10 ROWS FETCH FIRST 5 ROWS ONLY;

 

List all artists and use the FETCH FIRST construction to pick one arbitrary album for each artist.

SELECT a.artist,

      (SELECT p.product

       FROM mimer_store.products AS p

         JOIN mimer_store.items AS i ON p.product_id = i.product_id

         JOIN mimer_store_music.titles AS t ON i.item_id = t.item_id

       WHERE t.artist_id = a.artist_id

       FETCH FIRST 1 ROW ONLY) AS work_sample

FROM mimer_store_music.artists AS a;

 

Find the 10 most common starts of artist names, leading ‘The’ excluded:

SELECT strt, count(*) AS cnt

FROM

(

    SELECT CASE WHEN artist NOT LIKE 'The %' THEN CAST(artist AS nchar(3))

                ELSE SUBSTRING(artist FROM 5 FOR 3)

           END AS strt

    FROM mimer_store_music.artists

) AS a

GROUP BY str

ORDER BY cnt DESC

FETCH FIRST 10 ROWS ONLY;

Standard Compliance