Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site


SELECT


Retrieves data from the tables in the database.



Usage

Embedded/Interactive/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 OF Clause

If the SELECT statement defines a cursor for UPDATE CURRENT statements, a for-update-of clause may be optionally used to list the columns to be updated. If the for-update-of clause 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):

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

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 5 ROWS ONLY;
 

Mimer SQL Experience
List all artists and use the FETCH FIRST construction to pick one 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 1) 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)
 GROUP BY strt
 ORDER BY cnt DESC
 FETCH FIRST 10;
 

Standard Compliance

Standard
Compliance
Comments
SQL-2011
Core
Mimer SQL Experience:
Fully compliant.
Mimer SQL Engine:
Sub-feature E051-09, "Rename columns in the FROM clause" is not supported.
Sub-feature E071-03, "EXCEPT DISTINCT" is not supported.
SQL-2011
Features outside core
Feature T551, "Optional keywords for default syntax" support for the keyword DISTINCT.
Feature F857, "Top-level <fetch first clause> in <query expression>"
Feature F861, "Top-level <result offset clause> in <query expression>".
Mimer SQL Experience:
Feature F302, "INTERSECT table operator".
Feature F304, "EXCEPT ALL table operator".
Feature F851, "<order by clause> in subqueries".
Feature F855, "Nested <order by clause> in <query expression>".
Feature F856," Nested <fetch first clause> in <query expression>".
Feature F858, "<fetch first clause> in subqueries".
Feature F860, "dynamic <fetch first row count> in <fetch first clause>".
Feature F862, "<result offset clause> in subqueries".
Feature F865, "dynamic <offset row count> in <result offset clause>".

Mimer SQL Extension
Support for host variable in <fetch first clause> and <result offset clause> is a Mimer SQL extension.


Mimer
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
info@mimer.se
Mimer SQL Documentation TOC PREV NEXT INDEX