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

Organization of this subsection
(1) Function
(2) Format
(3) Operands
(4) Rules for derived query expressions in WITH clauses
(5) Rules for the query expression body
(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
    • Component specification
  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.

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, 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 derived query expressions in WITH clauses

  1. The attributes of columns specified as query names for a derived query expression in a WITH clause (presence of data type, data length, and NOT NULL constraint) are the same as the attributes of columns corresponding to the derived table specified for a derived query expression in a WITH clause.
  2. Specify the base table name or view table name to be used in the FROM clause of a derived query expression in a WITH clause. A query name cannot be specified.
  3. [table-specification.]ROW cannot be specified in a selection expression of a derived query expression specified in the WITH clause.
  4. A subscripted repetition column cannot be specified in a directly included SELECT clause in a derived query expression specified in the WITH clause.
  5. The following value expressions cannot be specified in a derived query expression specified in the WITH clause:
    • XML constructor function
    • SQL/XML scalar function
    • SQL/XML predicate
    • SQL/XML set function
  6. A value expression other than a column specification cannot be specified in a GROUP BY clause of a derived query expression specified in a WITH clause.
  7. If a CASE expression is specified in a selection expression of the outermost query specification specified in a WITH clause, a repetition column cannot be specified in that CASE expression's search conditions.
  8. The following items cannot be specified in a selection expression in a derived query expression specified in a WITH clause:
    • WRITE specification
    • GET_JAVA_STORED_ROUTINE_SOURCE specification
    • Window function

(5) Rules for the query expression body

  1. When the WITH clause is used, a 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, single-row SELECT statements cannot be specified in the query expression body.
  3. 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)

  4. If the column derived from a view definition or a derived query expression specified in a WITH clause is COUNT(*) or COUNT_FLOAT(*), that derived column cannot be used as an external reference 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)

  5. 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
    • Abstract data type
    • Repetition column
  6. If a column derived from a function invocation, component specification, SQL function, or SQL parameter with 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 defined length of 32,001 bytes
    • Abstract data type
  7. If a column derived from a view definition or a derived query expression specified in a WITH clause is derived from a value expression other than a column specification, a component specification cannot be specified for that derived column.
  8. If a column derived from a view definition or a derived query expression specified in a WITH clause is derived with the component specification, that derived column cannot be used as an external reference in the query expression body.
  9. An error occurs if either a function that receives inter-function values (receive function for passing inter-function values) or a function that sends inter-function values (send function for passing inter-function values) of the functions provided by a plug-in is specified and any of the following applies to the query in the defined view table:
    • The CASE clause in the view definition or the receive function for passing inter-function values specifies the scalar function VALUE.
    • The view definition specifies a receive function for passing inter-function values for which there are multiple instances of the send function for passing inter-function values, and the first arguments of the send functions for passing inter-function values are the same.
    • The view definition specifies either the receive function for passing inter-function values or the send function for passing inter-function values, and the query specification that specified the view table creates an inner derived table. For details about the conditions for creating an inner derived table, see 2.21 Inner derived table.
    • The send function for passing inter-function values that corresponds to the receive function for passing inter-function values specified in the view definition does not exist in either the view definition or the query specification that specified the defined view table.
    • The send function for passing inter-function values that corresponds to the receive function for passing inter-function values in the query specification that specified the view table does not exist in either the query specification or the query that defined the view table.

(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. If the columns of a derived table that are objects of a set operation are of the character string data type, make sure the character set specified for all of the corresponding columns is the same. However, if the columns of a derived table specified in a second or later query specification are the value expression listed below, they are converted to the character set of the columns of the derived table specified in the first query specification:
    • Character string literal
  3. 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).
  4. 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
  5. 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.
  6. 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).
  7. In a set operation, a query expression body cannot be specified in the FROM clause of a query specification.
  8. 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
  9. 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.
  10. 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.
  11. 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 indicates 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)
    The specified values of the pd_sql_dec_op_maxprec operand are used to determine the precision as follows.
    pd_sql_dec_op_maxprec operand valuePrecision of result column of set operation operand and any corresponding columnsPrecision obtained with max(p1 - s1, p2 - s2) + max(s1, s2)Precision of result column of set operation operand
    29 or omittedAll columns are 29 or fewer digits wide29 or fewer digitsPrecision obtained with max(p1 - s1, p2 - s2) + max(s1, s2)
    30 or more digitsError
    Includes columns that are 30 or more digits wide38 or fewer digitsPrecision obtained with max(p1 - s1, p2 - s2) + max(s1, s2)
    39 or more digitsError
    38Any38 or fewer digitsPrecision obtained with max(p1 - s1, p2 - s2) + max(s1, s2)
    39 or more digitsError
    For details about the pd_sql_dec_op_maxprec operand, see the manual HiRDB Version 9 System Definition.
    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.
  12. 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 indicates 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 9 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