7.1.1 Specification format and rules for query expressions

(a) WITH-clause

WITH-clause ::= WITH  WITH-list-element[,WITH-list-element]...
  WITH-list-element ::= query-name [(WITH-column-list)] AS (query-expression-body [LIMIT-clause]) [maximum-number-of-recursions-specification]
    WITH-column-list ::= column-name[,column-name]...
    maximum-number-of-recursions-specification ::= /*>> MAX RECURSION maximum-number-of-recursions <<*/

Specify the WITH clause if you want to define the result of the query specified by AS (query-expression-body) to be held in a temporary derived table. The following figure shows an example of specifying a WITH clause.

query-name:

Specifies the name of the derived table. The name specified here is defined as the name of the query. You cannot specify the same query name as the one in the WITH clause.

WITH-column-list:

Specify a column name for each column in query-name (the derived table).

The number of column names specified in WITH-column-list must be the same as the number of columns derived by the outermost query in the query expression body in the corresponding AS (query-expression-body).

If WITH-column-list is omitted, the names of the columns in query-name will be the names of the columns derived by the outermost query in the query expression body in the corresponding AS (query-expression-body). For rules on derived column names, see 6.9 Derived column names.

Note the following points:

• The column names in WITH-column-list must be unique.

• If WITH-column-list is omitted, the column names that are derived by the query expression body must be unique.

• Do not specify a character string in the EXPnnnn_NO_NAME format as a column name in WITH-column-list. Such a column name might duplicate a derived column name that is automatically set by HADB. In this format, nnnn is an unsigned integer in the range from 0000 to 9999.

• The number of columns derived by the outermost query in the query expression body specified in the corresponding AS (query-expression-body) cannot exceed 1,000.

AS (query-expression-body [LIMIT-clause]):

Specifies a query expression body.

The derived table is created from the query expression body specified here. The name of the derived table will be the name specified in query-name.

Note that you cannot specify a dynamic parameter inside the query expression body.

LIMIT-clause:

Specifies the maximum number of rows that will be retrieved from the results of the query expression body.

For details about the LIMIT clause, see 7.9 LIMIT clause.

Note that you cannot specify the LIMIT clause for a recursive query.

maximum-number-of-recursions-specification:

maximum-number-of-recursions-specification ::= /*>> MAX RECURSION maximum-number-of-recursions <<*/

Specifies the maximum number of recursions that can be performed when a recursive query is made. Use an unsigned integer literal to specify the maximum number of times recursion can be performed. The following rules apply:

• If the maximum-number-of-recursions specification is omitted, 100 is assumed as the maximum number of recursions.

• Specify an unsigned integer literal in the range from 0 to 32,767 as the maximum number of recursions.

• If the number of times recursion is performed exceeds the maximum number of recursions, the SQL statement will result in an error.

• If you specify 0 as the maximum number of recursions, recursion can be performed indefinitely. Therefore, if you specify 0, execution of the SQL statement might be repeated indefinitely.

• If no recursive query is specified, the maximum-number-of-recursions specification is invalid.

(b) query-expression-body

query-expression-body::={query-term
                  |query-expression-body {UNION|EXCEPT} [{ALL|DISTINCT}][set-operation-method-specification] query-term}
  query-term::={query-primary|query-term INTERSECT [{ALL|DISTINCT}] query-primary}
    query-primary::={query-specification|(query-expression-body)}
  set-operation-method-specification::=/*>> SET OPERATION NOT BY HASH <<*/

Specify one of the following in query-expression-body:

• query-term

• A set operation that determines the union or difference of the tables derived from a query-expression-body and a query-term.

Note that UNION ALL, UNION DISTINCT, EXCEPT ALL, EXCEPT DISTINCT, INTERSECT ALL, and INTERSECT DISTINCT are referred to as set operators.

query-term:

Specify one of the following in query-term:

• query-primary

• A set operation that determines the intersection of a query-term and a query-primary

{UNION | EXCEPT}:

Specify UNION to determine the union, or EXCEPT to determine the difference.

{ALL|DISTINCT}:

Specify whether to eliminate duplicate rows in the results of the set operation.

ALL: Do not eliminate duplicate rows in the results of the set operation.

DISTINCT: If there are duplicate rows in the results of the set operation, or in the operands of the set operation, consolidate the duplicate rows into a single row.

If neither ALL nor DISTINCT is specified, the system assumes that DISTINCT is specified.

query-primary:

In query-primary, specify a query-specification or (query-expression-body).

INTERSECT:

Specify this to determine the intersection.

query-specification:

Specifies a query specification. For details about query specifications, see 7.2 Query specification.

(query-expression-body):

Specifies a query expression body.

set-operation-method-specification:

If a set operation method specification is used, a processing method other than hash execution is used as the method for processing the set operation. For details about the method for processing the set operation, see Methods for processing set operations in the HADB Application Development Guide.

Note that, normally, there is no need to specify this. If the set operation method specification is omitted, HADB determines the method for processing the set operation.

The following shows the rules that apply when a set operation method specification is used:

• A set operation method specification that is specified for the EXCEPT set operation is ignored.

• A set operation method specification is applied to all set operations in a query expression body (all occurrences of UNION DISTINCT or UNION ALL). Whether a set operation method specification is applied can be checked in the access path information. For details about access path information, see Set operation method specification in the HADB Application Development Guide.

Example

SELECT "C1" FROM "T1"
UNION                                        ...[1]
SELECT "C1" FROM "T2"
UNION /*>> SET OPERATION NOT BY HASH <<*/    ...[2]
SELECT "C1" FROM "T3"

As shown in the preceding example, if a set operation method specification is written for the set operation on row [2], the set operation method specification is also applied to the set operation on row [1] (underlined portion).

(a) Rules for the WITH clause

1. When there is one query name, the effective scope of the query name does not extend beyond the query expression body that follows the WITH clause. When there are two or more query names, the effective scope is different for each query name. For examples of the effective scope of a query name, see (1) The effective scope of scope variables specified in the FROM clause of a SELECT statement in 6.8.3 Effective scope of scope variables.

2. In the query expression body of a WITH list element, subqueries can be specified in a nested form. In this case, the subquery nesting depth must not exceed 31. Note that if the table specified in the FROM clause is a viewed table or query name, the subquery nesting depth after HADB generates the internal derived table to the viewed table or query name must not exceed 31. For details, see (a) Common rules for subqueries in (4) Rules in 7.3.1 Specification format and rules for subqueries.

   Example 1:

   WITH "Q1" AS 
     (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM
     (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM
     (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM
     (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM
     (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM
     (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM
     (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM "T1") AS DT32
     ) AS DT31 ) AS DT30 ) AS DT29 ) AS DT28 ) AS DT27 ) AS DT26 ) AS DT25 ) AS DT24
     ) AS DT23 ) AS DT22 ) AS DT21 ) AS DT20 ) AS DT19 ) AS DT18 ) AS DT17 ) AS DT16
     ) AS DT15 ) AS DT14 ) AS DT13 ) AS DT12 ) AS DT11 ) AS DT10 ) AS DT9 ) AS DT8
     ) AS DT7 ) AS DT6 ) AS DT5 ) AS DT4 ) AS DT3 ) AS DT2 ) AS DT1 ) AS DT0 )
   SELECT * FROM "Q1"

   In the preceding example, the subquery nesting depth of query name Q1 is 32. In this case, because the maximum nesting depth is exceeded, the SELECT statement will result in an error.

   Note that in this example, T1 is the base table.

   Example 2:

   WITH "Q2" AS 
     (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM
     (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM
     (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM
     (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM
     (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM
     (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM
     (SELECT * FROM (SELECT * FROM "T1") AS DT30
     ) AS DT29 ) AS DT28 ) AS DT27 ) AS DT26 ) AS DT25 ) AS DT24 ) AS DT23 ) AS DT22
     ) AS DT21 ) AS DT20 ) AS DT19 ) AS DT18 ) AS DT17 ) AS DT16 ) AS DT15 ) AS DT14
     ) AS DT13 ) AS DT12 ) AS DT11 ) AS DT10 ) AS DT9 ) AS DT8 ) AS DT7 ) AS DT6
     ) AS DT5 ) AS DT4 ) AS DT3 ) AS DT2 ) AS DT1 ) AS DT0 ),
     "Q3" AS (SELECT * FROM "Q2"),
     "Q4" AS (SELECT * FROM "Q3")
   SELECT * FROM "Q4"

   • For query name Q2, the subquery nesting depth is 30. In this case, the maximum nesting depth is not exceeded.

   • For query name Q3, the subquery nesting depth becomes 31 when the internal derived table is generated. In this case, the maximum nesting depth is not exceeded.

   • For query name Q4, the subquery nesting depth becomes 32 when the internal derived table is generated. In this case, because the maximum nesting depth is exceeded, the SELECT statement will result in an error.

   Note that in this example, T1 is the base table.

   Example 3:

   WITH "Q5" AS (SELECT "C1" FROM (SELECT "C1" FROM "T1") AS DT
                 UNION ALL
                 SELECT "C1"+1 FROM "Q5" WHERE "C1"+1 < 5),
        "Q6" AS (SELECT * FROM
                 (SELECT * FROM
                  (SELECT * FROM
                   (SELECT * FROM
                    (SELECT * FROM
                     (SELECT * FROM "Q5") AS DT4
                    ) AS DT3
                   ) AS DT2
                  ) AS DT1
                 ) AS DT0),
         "Q7" AS
         (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM
         (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM
         (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM
         (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM
         (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM
         (SELECT * FROM "Q6") AS DT23
         ) AS DT22 ) AS DT21 ) AS DT20 ) AS DT19 ) AS DT18
         ) AS DT17 ) AS DT16 ) AS DT15 ) AS DT14 ) AS DT13
         ) AS DT12 ) AS DT11 ) AS DT10 ) AS DT9 ) AS DT8
         ) AS DT7 ) AS DT6 ) AS DT5 ) AS DT4 ) AS DT3
         ) AS DT2 ) AS DT1 ) AS DT0 )
   SELECT * FROM "Q7"

   • For query name Q5, the subquery nesting depth is 1. In this case, the maximum nesting depth is not exceeded.

   • For query name Q6, the subquery nesting depth becomes seven when the internal derived table is generated. In this case, the maximum nesting depth is not exceeded.

   • For query name Q7, the subquery nesting depth becomes 32 when the internal derived table is generated. In this case, because the maximum nesting depth is exceeded, the SELECT statement will result in an error.

   Note that in this example, T1 is the base table.

(b) Rules for recursive queries

1. A recursive query must include the following items: one or more query specifications^# that do not include a recursive query name that references the recursive query, and one or more query specifications^# that include a recursive query name that references the recursive query. Query specifications^# that do not include a recursive query name that references the recursive query are called anchor members, and query specifications^# that include a recursive query name that references the recursive query are called recursive members.

   #: Subqueries do not apply.

2. To specify multiple recursive members, make sure that each of them is an operand of a set operation (UNION ALL).

3. Also, make sure that the last anchor member and first recursive member that are specified in a recursive query are operands of a set operation (UNION ALL). The following shows an example of specifying anchor and recursive members.

   Example:

   WITH "Q1"("C1","C2")
       AS (SELECT "C1","C2" FROM "T1" WHERE "C1" > 0                          ...1
           UNION ALL
           SELECT "Q1"."C1"+1,"T1"."C2" FROM "Q1","T1" WHERE "Q1"."C1" < 5)   ...2
   SELECT * FROM "Q1"

   [Explanation]

   1. The underlined entry is an anchor member.

   2. The underlined entry is a recursive member.

4. All anchor members must be specified before the first recursive member specified in the recursive query.

5. In the FROM clause of a recursive member in a recursive query, two or more recursive query names that reference the recursive query cannot be specified.

6. In a subquery of a recursive query, a recursive query name that references the recursive query cannot be specified.

7. The following items cannot be specified in recursive members. The following items also cannot be specified in subqueries in recursive members.

   • SELECT DISTINCT

   • GROUP BY clause

   • HAVING clause

   • LIMIT clause

   • Set functions

   • LEFT OUTER JOIN

   • RIGHT OUTER JOIN

   • FULL OUTER JOIN

   However, the preceding items can be specified in the following locations:

   • A viewed table specified in a recursive member

   • A viewed table specified in a subquery in a recursive member

   • A derived query derived from a query name

8. The following two items must have the same data type and data length:

   • Data type and data length of the columns^#1 that make up the table that is derived from the result of a set operation for all recursive members that are specified in the recursive query

   • Data type and data length of the columns^#2 that make up the table that is derived from the result of a set operation for all anchor members

   #1: If there is only one recursive member, the selection expression of the recursive member applies.

   #2: If there is only one anchor member, the selection expression of the anchor member applies.

9. An overview of a recursive query search performed under the following conditions is described later:

   • The query expression body that performs a set operation for all anchor members that are specified in the recursive query is Q₀.

   • The search result of Q₀ is X₀.

   • The query expression body that performs a set operation for all recursive members that are specified in the recursive query is Qi.

   • The search result of Qi is Xi.

   • The number of recursions is i.

   Overview of a recursive query search

   1. A search is performed with Q₀ (the search result is X₀).

   2. The search result X₀ becomes the result of the recursive query.

   3. Based on the previous search result X_i-1, a search is performed with Qi (the search result is Xi).

   4. Either of the following operations is performed according to the search result Xi:

   • If the search result Xi is not a null row, the search result Xi becomes the result of the recursive query, and the processing returns to step 3.

   • If the search result Xi is a null row, the recursive query terminates.

   Example:

   WITH "REC"("VAL") AS (
       SELECT * FROM (VALUES(1))                          <= Anchor member
       UNION ALL
       SELECT "VAL" + 1 FROM "REC" WHERE "VAL" + 1 <= 5   <= Recursive member
       )
   SELECT "VAL" FROM "REC"

   Example of running the preceding SQL statement

   

   When the preceding SQL statement is run, recursion occurs four times.

(c) Rules for set operations

1. In the evaluation order of set operations, parentheses take precedence over INTERSECT, which takes precedence over UNION and EXCEPT.

2. Derived tables returned by a query-term and query-term, a query-term and query-primary, or a query-primary and query-primary combination that are targeted by the set operation are treated as a set of rows, on which the set operation is performed.

3. The number and order of the columns must be identical in the tables targeted by the set operation (the derived tables returned by the query terms).

   Example:

   

   Furthermore, the data types of the corresponding columns must be data types that can be compared. In the above example, column C1 in table T1 and column C1 in table T2 must have data types that can be compared. Similarly, column C2 in table T1 and column C2 in table T2 must have data types that can be compared.

   For details about data types that can be compared, see (1) Data types that can be compared in 6.2.2 Data types that can be converted, assigned, and compared.

   However, the following data cannot be compared with the set operation:

   • DATE type data cannot be compared to character string data (even to the predefined input representation of a date).

   • TIME type data cannot be compared to character string data (even to the predefined input representation of a time).

   • TIMESTAMP type data cannot be compared to character string data (even to the predefined input representation of a time stamp).

   For information about predefined input representations, see 6.3.3 Predefined character-string representations.

4. The column names of a table derived by a set operation are determined by the column names of the tables derived by the query terms specified in the set operation. For rules about the column names of tables derived from set operations, see (1) In the case of a query expression in 6.9.2 Decision rules for derived column names in query results.

5. The number and order of columns in the derived table produced by the set operation will be the same as the columns that make up the corresponding tables that were targeted by the set operation (the derived tables returned by query terms). Note that if even one of the corresponding columns does not have the NOT NULL constraint (null values are allowed), the set operation is executed without the NOT NULL constraint on all the columns of the derived table (null values are allowed).

6. The data types and data lengths of the columns in the derived table produced by the set operation are determined by the data types and data lengths of the columns that make up the corresponding tables that were targeted by the set operation (the derived tables returned by the query terms). For details, see 7.20.2 Data types of the results of value expressions.

7. Q1 and Q2 are set operation operands of the set operation. In this case, the number of occurrences of a given row in the results of Q1 set-operation Q2 is as shown in the following table.

   Table 7‒1: Number of occurrences of a given row in the results of the set operation

   Set operator	Number of occurrences of a given row R in the results of the set operation
   	When ALL is not specified	When ALL is specified
   UNION	0 (when m = 0 and n = 0) 1 (when m > 0 or n > 0)	m + n
   EXCEPT	0 (when m = 0 or n > 0) 1 (when m > 0 and n = 0)	MAX(m - n, 0)
   INTERSECT	0 (when m = 0 or n = 0) 1 (when m > 0 and n > 0)	MIN(m, n)

   Notes:

   In the table, m represents the number of occurrences of R in Q1, and n represents the number of occurrences of row R in Q2.

8. If the set operations specified in the SQL statement specified in the query expression body are all UNION, a maximum of 1,023 set operations can be specified. However, if the specified set operations include EXCEPT or INTERSECT, no more than 63 set operations can be specified.

   Note that when a viewed table is specified in an SQL statement, HADB uses an internal derived table based on the query expression specified in the CREATE VIEW statement. The rules for the maximum number of set operations apply to this internal derived table.

9. A maximum of 63 outer joins with FULL OUTER JOIN specified as the joined table mode can be specified in the SQL statement specified in the query expression body.

10. A set operation specified in a set operation that has DISTINCT specification might be treated as one having DISTINCT specification.