2.3 Query specification

Organization of this section
(1) Function
(2) Privileges
(3) Format
(4) Operands
(5) Rules
(6) Notes

(1) Function

A query specification derives a table composed of retrieval results containing columns of selection expressions, by specifying retrieval conditions (table expressions) for a table and items (selection expressions) in which the retrieval results are to be output. The table obtained in this manner is called a derived table.

(2) Privileges

A user with the SELECT privilege for a table can execute a query specification to retrieve that table.

(3) Format

SELECT[{ALL|DISTINCT}]{selection-expression[, selection-expression]...
    |*}table-expression

(4) Operands

For a retrieval that produces duplicated rows (identical rows composed of items specified in the selection expressions), specifies whether the duplicates are to be retained or eliminated.

Eliminating duplicate rows is called duplicates exclusion.

ALL
Specifies that duplicated rows are to be retained in the retrieval results.
DISTINCT
Specifies that duplicated rows are to be output as a single row.

Specifies the items to be output as retrieval results.

selection-expression
The following can be specified as a selection expression:
  • Column specification
  • [table-specification.]ROW
  • Set functions
  • Window function
  • Scalar functions
  • CASE expressions
  • CAST specification
  • Literals
  • USER
  • CURRENT_DATE
  • CURRENT_TIME
  • CURRENT_TIMESTAMP[(p)]
  • SQL variable or SQL parameter
  • Value expressions * [[AS] column-name]
  • table-specification.*
  • Component specification
  • Function call*
  • WRITE specification
  • GET_JAVA_STORED_ROUTINE_SOURCE specification
  • Scalar subquery
* The ? parameter and embedded variables can be specified as arguments in a function call. For the specification details, see 3 in subsection (5) below. ? parameters and embedded variables can be specified in the arguments for the scalar functions LENGTH, SUBSTR, and POSITION. See the rules on these functions for specification methods.
The following rules apply to selection expressions:
  1. The ? parameter and the embedded variable can be specified in the arguments for the scalar functions LENGTH, SUBSTR, and POSITION. The ? parameter and the embedded variable can be specified in the argument for LENGTH, in the first argument of SUBSTR, or in the first and second arguments of POSITION only if their data type is BLOB or BINARY, and if the AS data type is specified.
  2. Value expressions that yield the following type of result cannot be specified in a selection expression:
    • BOOLEAN
  3. Value expression that yield results of the abstract data type cannot be specified in a cursor declaration, a dynamic SELECT statement, a single-row SELECT statement, a derived table in a FROM clause, or a subquery in a predicate.
  4. When SELECT DISTINCT is specified, a set function with the DISTINCT specification cannot be specified in a selection expression or table expression.
  5. When a repetition column is specified in a selection expression, none of the following items can be specified:
    • SELECT DISTINCT
    • UNION [ALL] with respect to that query
    • EXCEPT [ALL] with respect to that query
  6. [table-specification.]ROW can be specified only for a base table with the FIX attribute. ROW refers to an entire row, and specifying ROW retrieves into one area an entire row as a single data item. Regardless of the data types of the columns that compose the row, the data type of the row that is retrieved is the ROW type. (Either a variable associated with CHAR(n), where n denotes the row length, or a structure can be specified for the ROW type; however, the structure specified for the ROW type should not contain spaces for boundary alignment.) The data length of the resulting row is its row length (sum of the data lengths of the columns that compose the row). If ROW is specified in a selection expression, none of the following can be specified in a query specification related to the selection expression:
    • Set functions
    • GROUP BY clause
    • UNION [ALL] or EXCEPT [ALL]
  7. When using the ROW type, make sure that the platform on which the UAP runs and the platform on which the HiRDB server runs have the same endian. The ROW type cannot be used between applications using different endian types. For example, if the ROW type is used in a Windows UAP, the HiRDB server should also use the same endian order in the Windows version.
  8. [table-specification.]ROW cannot be specified for an inner table of a joined table.
  9. Any column specified in a SELECT clause must reference the table that is specified in the FROM clause of that table expression.
  10. If a set function, GROUP BY clause, or HAVING clause is specified in a table expression, any column specification in the SELECT clause must be one of the following:
    • Grouping column (value expression specified in the GROUP BY clause)
    • Specification in an argument of a set function
  11. If an SQL variable or SQL parameter of any of the following data types is specified in a selection expression, the functions and clauses indicated below cannot be specified in a query specification that directly includes the SELECT clause or in a query that includes the SELECT clause:
    Data type of SQL variable or SQL parameter
    • BLOB
    • BINARY with a minimum definition length of 32,001 bytes
    • BOOLEAN
    • Abstract data type
    Functions and clauses that cannot be specified simultaneously in a query specification
    • Set functions
    • GROUP BY clause
    • HAVING clause
    • DISTINCT
    • Specifying in the FROM clause a view table specifying any of the facilities described previously or clauses in a derived query expression in a view definition.
    Functions and clauses that cannot be specified simultaneously in a query
    • Set operations
    • ORDER BY clause
    • FOR UPDATE clause
    • FOR READ ONLY clause
  12. Specifying table-specification.* means that all columns in the specified table are to be output in the order in which they were specified when the table was defined.
  13. AS column-name is specified in order to assign a name to a selection expression.
  14. When AS column-name is specified in the nth selection expression, the column name will be the nth column name in the table that is derived by the query specification that specified the selection expression.
  15. When AS column-name is not specified in the nth selection expression, the column name that is specified in the selection expression will be the nth column name in the table that is derived by the query specification. If the selection expression contains more than the column specification, the nth column will be a column without a name.
  16. If a scalar subquery is specified in an n-th selection expression, the n-th column name derived by the query specification will be the column name that is derived by a query specification of the scalar subquery. However, if an AS column name is specified, the column name will be the one specified in AS column-name. If the column derived by the query specification in the scalar subquery is a nameless column, the n-th column that is derived by the query specification will be a nameless column.
  17. A WRITE specification can be specified alone in a selection expression.
  18. A GET_JAVA_STORED_ROUTINE_SOURCE specification can be specified as follows:
    • Specified alone in a selection expression that is specified in the outermost query
    • Specified as an argument in the LENGTH scalar function in a selection expression that is specified in the outermost query
  19. DISTINCT is mutually exclusive with a WRITE specification or GET_JAVA_STORED_ROUTINE_SOURCE specification.
  20. A WRITE specification and GET_JAVA_STORED_ROUTINE_SOURCE specification cannot be specified in a selection expression in a query specification in an INSERT statement.
  21. If a GROUP BY clause specifying a value expression other than a column specification is specified in a table expression, that grouping column cannot be referenced from the scalar subquery that is specified in the selection expression.
  22. When the window function is specified as a selection expression, you must specify at least one selection expression in addition to the window function.
  23. You cannot specify the window function in a scalar operation.
  24. When the window function is specified, you cannot specify a set function as a selection expression.
*
Specifies that all table columns are to be output.
Specification of an asterisk indicates that all columns in all the tables specified in the FROM clause of the query specification are specified in the order of their specification in the table specified in the FROM clause. In this case, the table columns are arranged in the same order as when they were specified during table definition.

A table expression specifies one or more tables or externally joined tables that are the objects of retrieval. In a table expression it is also possible to specify the conditions under which tables are to be searched or joined (search conditions and grouping). For details about table expressions, see 2.5 Table expressions.

For restrictions on derived query expressions in a view definition statement, see CREATE VIEW (Define view) in Chapter 3. Definition SQL.

(5) Rules

  1. In a query specification with respect to the named derived table that has been derived from a value expression specifying the following attributes in the selection expression in the outermost query, a query for creation of an internally derived table cannot be specified (for the conditions under which an internally derived table can be created, see 2.21 Inner derived tables):
    • BLOB
    • BINARY with a minimum length of 32,001 bytes
    • Repetition column
  2. When a ? parameter or an embedded variable is specified as an argument in a function call, the argument must be specified in the following format:
    • ? AS data-type
    • :embedded-variable [:indicator-variable] AS data-type

(6) Notes

  1. When retrieval or updating is performed in units of rows (ROW specification), the length of the date data type that is specified in an embedded variable, an SQL variable, or an SQL parameter relative to ROW is 4 bytes in X'YYYYMMDD' format.
    If date data is passed or received in a character expression using a row-by-row basis (ROW specification) interface, the column must be defined as CHAR(10), rather than as a date data column. Any date operation should be specified by converting the data into the date data type with the DATE scalar function.
  2. When a retrieval or updating is performed in units of rows (ROW specification), the length of the time data type that is specified in an embedded variable, an SQL variable, or an SQL parameter relative to ROW is 3 bytes in X'HHMMSS' format.
    If time data is passed or received in a character expression using a row-by-row basis (ROW specification) interface, the column must be defined as CHAR(8), rather than as a time data column. Any time operation should be specified by converting the data into the time data type with the TIME scalar function.
  3. For performing retrieval or updating in units of rows (ROW specification), the length of the time data type part of an embedded variable, SQL variable, or SQL parameter is (7 + (p[Figure] 2)) bytes in an X'YYYYMMDDhhmmss[nn...n]' format. For passing or receiving time stamp data in character representations using a row-by-row interface (ROW specification), columns should be defined in CHAR of length 19, 22, 24, or 26 bytes instead of the time stamp data type.
  4. HiRDB may create a work table when any of the following conditions is satisfied:
    • DISTINCT is specified.
    • A value expression containing a set function is specified in a selection expression.
    • A value expression containing the window function is specified in a selection expression.
    In this process, the above processing may be subject to some restrictions depending on the row length of the work table. For details about work table row lengths, see the HiRDB Version 8 Installation and Design Guide.