2.2.1 Query expression format 1 (general-query-expression)

Organization of this subsection
(1) Function
(2) Format
(3) Operands
(4) Rules for the query expression body
(5) Rules for derived query expressions in the WITH clause
(6) Rules common to set operations
(7) Note
(8) Specification examples

(1) Function

A query specification specifies a combination of a derived query expression and the query expression itself in the WITH clause. When the WITH clause is used, the derived table produced by the derived query expression can be the query name, which can be specified in the query expression itself.

The query expression body specifies either a query specification or a set operation in order to determine the union set or the disjunction set between the derived tables that are obtained as a result of a query specification. UNION [ALL] must be specified when a union set is to be determined. EXCEPT [ALL] must be specified when a disjunction set is to be determined. A query expression can be specified in either a cursor declaration or a cursor specification in a dynamic SELECT statement.

(2) Format

query-expression::=[WITH query-name[(column-name[,column-name]...)]
                                AS(derived-query-expression)
                  [, query-name[(column-name[,column-name]...)]
                                AS(derived-query-expression)]...]
               query-expression-body
derived-query-expression::= query-expression-body
query-expression-body::= {query-specification
                 | (query-expression-body)
                 | query-expression-body {UNION | EXCEPT} [ALL]
                  {query-specification | (query-expression-body) }}

(3) Operands

WITH query-name
Specifies the name of the derived table to be specified in the table expression of the query expression body. Multiple query names specified in a WITH clause must all be unique.
column-name
Specifies a column name that corresponds to a column to be derived from the derived table specified by one of the derived query expressions in the WITH clause.
The following rules apply to column names:
  1. If no column name is specified, the default is determined as follows:
    • If no set operation is specified in the derived query expression in the WITH clause, the column name of the column in the derived table specified by the query specification in the WITH clause (if AS column-name is specified, the column name specified in the AS clause) becomes the column name of the query name for the WITH clause.
    • If a set operation is specified in the derived query expression in the WITH clause, the column name of the column in the derived table specified by the first query specification in the derived query expression (if AS column-name is specified, the column name specified in the AS clause) becomes the column name of the query name for the WITH clause.
    If the derived table specified in the derived query expression in the WITH clause contains two or more columns with the same column name or contains a column that does not have a name, column-name cannot be omitted.
  2. If a column in the derived table has been derived from any of the items listed below and AS column-name is omitted, that column becomes a nameless column:
    • Scalar operation
    • Function call
    • Set function
    • Literal
    • USER
    • CURRENT_DATE
    • CURRENT_TIME
    • CURRENT_TIMESTAMP[(p)]
    • SQL variable
    • SQL parameter
  3. Each query in a WITH clause must have a unique name.
  4. The number of columns specified by column names in a query name in a WITH clause must be the same as the number of columns in the derived table obtained as a result of the derived query expression in the WITH clause.
  5. A maximum of 30,000 columns can be specified by column names for a single query name in a WITH clause.

derived-query-expression::=query-expression-body

Specify a table expression, a column to be derived as a query name, a set operation, or whether duplicate-row exclusion is on. In the FROM clause contained in a table expression in query-expression-body, specify either a table name or a view table name.

When the WITH clause is used, specifies a table name, a view table name, or a query name in the FROM clause contained in the table expression in the query expression body.

When the WITH clause is used, the following items cannot be specified in a query expression body: an alias table defined for access to a table in another RD node, a table in another RD node, or a view table in another RD node.

When the WITH clause is used, a single-row SELECT statement cannot be specified in the query expression body.

Specifies the table expressions, the columns to be selected, or whether or not duplicates elimination is to be in effect for the rows to be selected.

See 2.3 Query specification for details about query specifications.

Specifies the order in which the set operations in the query expressions are to be evaluated when two or more query specifications joined by UNION or EXCEPT are specified (query-expression-body must be specified in parentheses).

Specifies that the union set or the difference set of derived tables produced by two query specifications or query expression bodies is to be determined. For details about set operations, see (6) below.

(4) Rules for the query expression body

  1. When the WITH clause is used, a table name, foreign table name, view table name, or query name must be specified in the FROM clause contained in the table expression in the query expression body.
  2. When the WITH clause is used, a table specifying an alias table, a table belonging to another RD node, or a view table belonging to another RD node cannot be specified in the query expression body.
  3. When the WITH clause is used, single-row SELECT statements cannot be specified in the query expression body.
  4. When a column derived by a derived query expression in a view definition or WITH clause is a set function whose argument is a value expression, the derived column cannot be used for external referencing in the query expression body.
    Example

    WITH QRY1 (QC1,QC2) AS (SELECT MAX(C1+C2),AVG(C1+C2)
                               FROM T1),
        QRY2 (QC1,QC2) AS (SELECT C1+C2,C3-C4 FROM T2)
     SELECT * FROM QRY1 X
        WHERE QC1>(SELECT QC1 FROM QRY2 WHERE QC2 = X.QC2)

  5. When a column derived by a derived query expression in a view definition or WITH clause is COUNT(*), the derived column cannot be used for external referencing in the query expression body.
    Example

    WITH QRY1 (QC1) AS (SELECT COUNT(*) FROM T1),
        QRY2 (QC1,QC2) AS (SELECT C1+C2,C3/C4 FROM T2)
     SELECT * FROM QRY1 WHERE EXISTS
        (SELECT * FROM QRY2 WHERE QC1 = QRY1.QC1)

  6. When you specify a table derived by specifying a value expression with any of the following attributes in the query expression body in a selection expression in the outermost query specification in a WITH clause, you cannot specify a query that creates an internally derived table (for the circumstances under which an internally derived table can be created, see 2.21 Inner derived tables):
    • BLOB
    • BINARY with minimum length of 32,001 bytes
    • Repetition column
  7. When a column derived from an SQL variable and an SQL parameter that has any of the following attributes is specified in a selection expression in the query expression body, FOR READ ONLY cannot be specified in a query that directly includes the SELECT clause:
    • BLOB
    • BINARY with a minimum definition length of 32,001 bytes

(5) Rules for derived query expressions in the WITH clause

  1. Columns that are derived as query names by a derived query expression in the WITH clause have the same attribute (data type, data length, whether or not subject to NULL constraints) as the corresponding columns in the derived table specified in the derived query expression in the WITH clause.
  2. Either a table name, foreign table name or a view table name should be specified in the FROM clause in the derived query expression in the WITH clause; a query name cannot be specified.
  3. In a selection expression, [table-specification.] ROW cannot be specified in a derived query expression in the WITH clause.
  4. Subscripted repetition columns cannot be specified in the SELECT clause that is directly included in a derived query expression in the WITH clause.
  5. Plug-in functions and component specifications cannot be specified in a derived query expression in a WITH clause.
  6. None of the following can be specified in a derived query expression in the WITH clause: a table specifying a table alias, a table belonging to another RD node, or a view table belonging to another RD node.
  7. Scalar operations, literals, function calls, and scalar subqueries of the following attributes cannot be specified in the selection expression in the outermost query in a derived query expression in the WITH clause or in the selection expression in a query that is an object of set operations:
    • BLOB
    • BINARY with a minimum length of 32,001 bytes
  8. A value expression containing any of the following data types cannot be specified in a derived query expression in a WITH clause:
    • BOOLEAN
    • Abstract data type
  9. Value expressions other than a column specification cannot be specified in the GROUP clause in a derived query expression in the WITH clause.
  10. If a CASE expression is specified in a selection expression in the outermost query specification in the WITH clause, a repetition column cannot be specified in a search condition in the CASE expression.
  11. The following items cannot be specified in a selection expression in a derived query expression in a WITH clause:
    • WRITE specification
    • GET_JAVA_STORED_ROUTINE_SOURCE specification
    • The SUBSTR scalar function that produces BLOB data type results
    • Function calls that produce BLOB data type results
    • Window function

(6) Rules common to set operations

  1. When a set operation is performed to obtain a union of sets or the difference between sets, the system performs the set operation by treating the two query specifications, derived query expressions, or query expression bodies that are subject to the operation as a set of rows in the derived table that is obtained as the result.
    Therefore, the derived tables that are subject to these set operations must have the same number of constituent columns in the same order. Similarly, the corresponding columns must have compatible data types for comparison operations. However, set operations cannot be performed between the following items: character string representations of date data items; character string representations of time data items; character string representations of time stamp data items; decimal representations of date interval data items; and decimal representations of time interval data items.
  2. The column names of a table derived by a set operation become the column names of the columns of the derived table specified by the first query specification of the query expression (if AS column-name is specified, the column name specified in the AS clause).
  3. If a column in the derived table has been derived from any of the items listed below and AS column-name is omitted, that column becomes a nameless column:
    • A value expression that includes an arithmetic operation, date operation, time operation, concatenation operation, or system built-in scalar function
    • CASE expression
    • CAST specification
    • Set function
    • Literal
    • USER
    • CURRENT_DATE
    • CURRENT_TIME
    • CURRENT_TIMESTAMP[(p)]
    • SQL variable
    • SQL parameter
    • Component specification
    • Function call
  4. A derived table obtained as a result of a set operation has the same number of constituent columns in the same order as the derived table that was subject to the set operation.
  5. Literals and the results of a set function, date operation, time operation, arithmetic operation, CASE expression, or CAST specification cannot be NOT NULL constrained (the null value must be allowed).
  6. In a set operation, a query expression body cannot be specified in the FROM clause of a query specification.
  7. When a set operation is performed, a value expression that produces a result with any of the following data types cannot be specified in a constituent column in the derived table that is subject to the set operation:
    • BLOB
    • BINARY with a minimum length of 32,001 bytes
    • BOOLEAN
    • Abstract data type
  8. When a set operation is specified, a repetition column cannot be included in the columns in the derived table that are subject to the set operation.
  9. When a set operation is specified, you cannot specify a WRITE specification, a GET_JAVA_STORED_ROUTINE_SOURCE specification, or the window function for a column of the target-derived table.
  10. The results of a set operation produce the following data types and data lengths:
    For character data, national character data, or mixed character data:
    • If any of the value expressions contains variable-length data, the result will be variable-length data.
    • The data length of the result will be the data length of the value expression with the largest data length.
    • If value expressions contain both character data and mixed character data, the data type of the result will be mixed character data.
    For numeric value data:
    The following table shows the data types of the results from set operations involving numeric data:
    Operand 1Operand 2
    SMALLINTINTEGERDECIMALSMALLFLTFLOAT
    SMALLINTSMALLINTINTEGERDECIMALSMALLFLTFLOAT
    INTEGERINTEGERINTEGERDECIMALFLOATFLOAT
    DECIMALDECIMALDECIMALDECIMALFLOATFLOAT
    SMALLFLTSMALLFLTFLOATFLOATSMALLFLTFLOAT
    FLOATFLOATFLOATFLOATFLOATFLOAT
    Set operation results of the DECIMAL data type have the following precision and scaling, where p and s denote the precision and scaling of operand 1, and p2 and s2 denote the precision and scaling of operand 2:
    Precision = max(p1-s1,p2-s2) + max(s1,s2)
    Scaling = max(s1,s2)
    If the precision of the result is 29 or less, the result is DECIMAL; if it is greater than 29, an error is generated.
    INTEGER is treated as DECIMAL (10,0); SMALLINT is treated as DECIMAL (5,0). If all corresponding constituent columns are NOT NULL constrained, the results are treated as NOT NULL constrained. Otherwise, the results are treated as not being NOT NULL constrained.
    Time stamp data
    Set operations can be performed between time stamp data items, producing the same data type as the source data type. If terms of a set operation include a fractional second precision, the fractional second precision of the result is MAX(p1, p2), where, given Q1 set operation Q2, p1 is the fractional second precision of Q1, and p2 is the fractional second precision of Q2.
    Binary data
    The data length of the result is the data length of the value expression with the largest data length.
  11. When ALL is specified, duplicated rows are left intact and handled as separate rows. When ALL is omitted, duplicated rows are consolidated into a single row (duplications are eliminated).
    The number of duplicated rows resulting from the operation "Q1 set-operation Q2" depends on whether ALL is specified, where Q1 denotes a query expression body (derived query expression) and Q2 denotes a query expression body (derived query expression) or a query specification.
    Let R represent the duplicate rows in Q1 or Q2, or Q1 and Q2; let m be the number of rows in Q1 in the duplicate row R; and let n be the number of rows in Q2 (m[Figure] 0, n[Figure] 0). If Q1 or Q2, or Q1 and Q2 do not contain duplicated rows, the system performs the set operation by assuming that there is one duplicated row R (m = 1, n = 0, or m = 0, n = 1). The following table shows the number of duplicate rows R resulting from each set operation:
    Set operationALL not specifiedALL specified
    UNIONmin (1, n+m)m+n
    EXCEPT1 (m>0 and n=0)
    0 (m=0 or n>0)
    max (m-n, 0)

(7) Note

  1. HiRDB may create a work table when the following conditions are satisfied:
    • Either UNION[ALL] or EXCEPT[ALL] is specified.
    In this operation, the above processing can be restricted depending upon the row length of the work table. For details about work table row lengths, see the HiRDB Version 8 Installation and Design Guide.

(8) Specification examples

WITH clause used

DECLARE CR1 CURSOR FOR
 WITH QRY1(QPCODE,QPNAME,QCOLOR,QSALES) AS
   (SELECT PCODE,PNAME,COLOR,PRICE*SQUANTITY FROM STOCK),
     SELECT QPNAME,MAX(QSALES) FROM QRY1 GROUP BY QPNAME

WITH clause not used

DECLARE CR1 CURSOR FOR
 SELECT PCODE,PNAME,COLOR,PRICE,SQUANTITY
    FROM STOCK