7.30.4 Conditions under which derived table expansion is not performed : Hitachi Advanced Database SQL Reference

If, in the outermost query specification for a derived query, you specify a derived query name with SELECT DISTINCT in the FROM clause, and one of the following specifications is in the query specification that directly includes that FROM clause:

GROUP BY clause, HAVING clause, or set function

The following is an example:

SELECT SUM("C1") FROM (SELECT DISTINCT * FROM "T1") AS "V1"
    GROUP BY "C2"

Table join (including joined table)

The following is an example:

SELECT * FROM (SELECT DISTINCT * FROM "T1") AS "V1","T1"
    WHERE "V1"."C1"="T1"."C1"

Selection expression in which a derived column derived from a value expression that includes a column specification is not specified as a single column specification

The following is an example:
```
SELECT "VC1" FROM (SELECT DISTINCT "C1","C2" FROM "T1") AS "V1"("VC1","VC2")
```
Because derived column VC2 is not specified in the selection expression, derived table V1 is not expanded.
```
SELECT "VC1"*1.05,"VC2" FROM (SELECT DISTINCT * FROM "T1") AS "V1"("VC1","VC2")
```
Because derived column VC1 is not specified as a single column specification in the selection expression, derived table V1 is not expanded.
Selection expression containing a value expression that includes scalar function RANDOM, scalar function RANDOM_NORMAL, scalar function RANDOMROW, a scalar subquery, or a window function

The following is an example:
```
SELECT "VC1","VC2",RANDOM()
    FROM (SELECT DISTINCT "C1","C2" FROM "T1") AS "V1"("VC1","VC2")
```
Selection expression in which a derived column derived from the following value expression is not specified as a single column specification: a value expression that includes scalar function RANDOM, scalar function RANDOM_NORMAL, scalar function RANDOMROW, or a set function, and does not include a column specification

The following is an example:
```
SELECT "VC1" FROM (SELECT DISTINCT "C1",RANDOM() FROM "T1") AS "V1"("VC1","VC2")
```
Sort key value expression that is not specified as a selection expression

The following is an example:
```
SELECT * FROM (SELECT DISTINCT * FROM "T1") AS "V1"
    ORDER BY ("C2"+1)
```

If, in the outermost query specification for a derived query, you specify a derived query name with a GROUP BY clause in the FROM clause, and one of the following specifications is in the query specification that directly includes that FROM clause:

A DISTINCT set function or inverse distribution function is specified.

The following is an example:

SELECT "C1",SUM(DISTINCT "C2")
    FROM (SELECT "C1","C2" FROM "T1" GROUP BY "C1","C2") AS "V1"
    GROUP BY "C1","C2"

Table join (including joined table)

The following is an example:

SELECT * FROM (SELECT "C1","C2" FROM "T1" GROUP BY "C1","C2")
    AS "V1","T1"

If, all of the following conditions are met: 1) In the outermost query specification for a derived query, the name of a derived query that contains the GROUP BY clause is specified for a FROM clause. 2) The query specification that directly contains that FROM clause also contains the GROUP BY clause, the HAVING clause, or a set function. 3) Either of the following conditions is met:

The number of grouping columns in the query specification for operating a derived query is different from the number of grouping columns in the derived query.

The following is an example:
```
SELECT "C1","C2" FROM (SELECT "C1","C2" FROM "T1" GROUP BY "C1","C2","C3") AS "V1"
    GROUP BY "C1","C2"
```

The derived column derived from the column referenced by a grouping column for a derived query is not specified alone in a grouping column that has a query specification for operating the derived query.

The following is an example:
```
SELECT "C1","C2"+1 FROM (SELECT "C1","C2" FROM "T1" GROUP BY "C1","C2") AS "V1"
    GROUP BY "C1","C2"+1
```

If, in the outermost query specification for a derived query, you specify a derived query name with a GROUP BY clause in the FROM clause including a value expression, and one of the following specifications is in the query specification that directly includes that FROM clause:

A grouping column of the derived query that was derived from a value expression that includes the column specification is specified as a column that makes an external reference.

The following is an example:
```
SELECT * FROM (SELECT "G1" FROM "T1" GROUP BY C1+1 "G1") AS "V1"
    WHERE EXISTS (SELECT * FROM "T2" WHERE "T2"."C1" = "V1"."G1")
```

If, in the selection expression of the outermost query specification for a derived query, you specify a derived query name with a value expression that includes a column specification in the FROM clause of that selection expression, and one of the following occurs in the query specification that directly includes that FROM clause:

A column of the derived query name that was derived from a value expression that includes a column specification is specified in a selection expression or in the HAVING clause as a grouping column that makes an external reference.

The following is an example:
```
SELECT "DC1" FROM (SELECT "C1"+1 AS "DC1" FROM "T1") AS "V1"
    GROUP BY "DC1"
    HAVING EXISTS (SELECT * FROM "T2" WHERE "T2"."C1" = "V1"."DC1")
```

Multiple inverse distribution functions are specified.

The following is an example:

SELECT MEDIAN("C1")
    FROM (SELECT ABS("C1") AS "C1","C2" FROM "T1") AS "V1"
    HAVING MEDIAN("C1")>100

A set function argument that makes an external reference to a column of the derived query name that was derived from a value expression that includes the column specification

The following is an example:
```
SELECT "C1" FROM (SELECT SUBSTR("C1",5) AS "C1","C2" FROM "T1") AS "V1"
    GROUP BY "C1"
    HAVING EXISTS(SELECT * FROM "T1" WHERE MAX("V1"."C1")="C1")
```

If, in the selection expression of the outermost query specification for a derived query, a derived query name (for which a value expression that does not include a column specification) is specified in the table reference of a joined table that is on the side filled with null values

The following is an example:

SELECT * FROM "T1" LEFT OUTER JOIN
                   (SELECT SUBSTR('ABC',2) AS "C1","C2" FROM "T2") AS "V1"
                   ON "T1"."C1"="V1"."C1"

If, in the selection expression of the outermost query specification for a derived query, you specify a derived query name with a value expression that does not include a column specification in the FROM clause of that selection expression, and one of the following occurs in the query specification that directly includes that FROM clause:

Multiple inverse distribution functions are specified.

The following is an example:

SELECT MEDIAN("C1")
    FROM (SELECT ABS(100) AS "C1","C2" FROM "T1") AS "V1"
    HAVING MEDIAN("C1")>100

A window function containing a derived query column name derived from a value expression that does not include a column specification

The following is an example:
```
SELECT "C2",SUM("C2") OVER(ORDER BY "C1")
     FROM (SELECT SUBSTR('ABC',2) AS "C1","C2" FROM "T1") AS "V1"
```

If, in the selection expression of the outermost query specification of a derived query, you specify, in a FROM clause, the name of a derived query in which a value expression that includes the scalar function RANDOM or RANDOM_NORMAL is specified, and you specify, in the SQL statement, a column derived from the scalar function RANDOM or RANDOM_NORMAL

The following is an example:

SELECT "C1", "C2" FROM (SELECT "C1"+RANDOM() AS "C1", "C2" FROM "T2") AS "V1"

If, all of the following conditions are met: 1) In the selection expression of the outermost query specification for a derived query, the name of a derived query that uses a value expression that includes the scalar function RANDOMCURSOR is specified for a FROM clause. 2) In the query specification that directly contains that FROM clause, the column derived from the value expression that includes scalar function RANDOMCURSOR for a derived query is specified in an item other than a selection expression and ORDER BY clause.

The following is an example:

SELECT "C1","C2"
    FROM (SELECT "C1"+RANDOMCURSOR(1,10,20) AS "C1","C2" FROM "T2") AS "V1"
      WHERE "C1">1000

If, all of the following conditions are met: 1) In the selection expression of the outermost query specification for a derived query, the name of a derived query that uses a value expression that includes the scalar function RANDOMROW is specified for a FROM clause. 2) The query specification that directly contains that FROM clause also contains the following specification:

An item (other than a selection expression and ORDER BY clause) in which the column derived from a value expression that includes scalar function RANDOMROW for a derived query is specified

The following is an example:
```
SELECT "C1","C2"
    FROM (SELECT "C1"+RANDOMROW(1,10,20) AS "C1","C2" FROM "T2") AS "V1"
      WHERE "C1">1000
```
A set function argument for which the column derived from a value expression that includes scalar function RANDOMROW for a derived query is specified

The following is an example:
```
SELECT SUM("C1")
    FROM (SELECT "C1"+RANDOMROW(1,10,20) AS "C1","C2" FROM "T2") AS "V1"
```
A window function in which the column derived from a value expression that includes scalar function RANDOMROW for a derived query is specified

The following is an example:
```
SELECT "C1",SUM("C2") OVER(ORDER BY "C1")
    FROM (SELECT "C1", "C2"+RANDOMROW(1,10,20) AS "C2" FROM "T2") AS "V1"
```
The scalar function RANDOMROW in which the column derived from a value expression that includes the scalar function RANDOMROW for a derived query is specified

The following is an example:
```
SELECT RANDOMROW(1,"C1","C2")
    FROM (SELECT "C1"+RANDOMROW(1,10,20) AS "C1","C2" FROM "T2") AS "V1"
```

Table join (including joined table)

The following is an example:

SELECT * FROM (SELECT "C1"+RANDOMROW(1,10,20) AS "C1","C2" FROM "T2")
    AS "V1","T1"

If, in the selection expression of the outermost query specification of a derived query, you specify a derived query name with a value expression that includes a dynamic parameter in the FROM clause of that selection expression, and you specify a column derived from the dynamic parameter in the query specification.

The following is an example:

SELECT "C1" FROM (SELECT SUBSTR("C1",?) AS "C1","C2" FROM "T1") AS "V1"

If, in the selection expression of the outermost query specification of a derived query, you specify a derived query name that specifies a value expression that includes a scalar subquery in the FROM clause of that selection expression.

The following is an example:

SELECT "C1" FROM (SELECT (SELECT "C1" FROM "T2") + 10 AS "C1"
                      FROM "T1") AS "V1"

If, in the selection expression of the outermost query specification of a derived query, you specify a derived query name with a value expression that includes an inverse distribution function in the FROM clause of that selection expression, and you specify a column derived from the inverse distribution function in the query specification.

The following is an example:

SELECT "C1" FROM (SELECT MEDIAN("C1") AS "C1",MAX("C2")
                      FROM "T1") AS "V1"

If, all of the following conditions are met: 1) In the selection expression of the outermost query specification for a derived query, the name of a derived query that uses a set function is specified for a FROM clause. 2) The query specification that directly contains that FROM clause also contains the following specification:

The GROUP BY clause that includes a value expression, DISTINCT set function, or inverse distribution function is specified

The following is an example:
```
SELECT SUM(DISTINCT "C1")
    FROM (SELECT COUNT("C1") AS "C1" FROM "T1") AS "V1"
    GROUP BY "C1"
```

Table join (including joined table)

The following is an example:

SELECT * FROM (SELECT COUNT("C1") AS "C1" FROM "T1") AS "V1","T1"
    WHERE "V1"."C1"="T1"."C1"

A set function argument for which a column of the derived query name that was derived from a set function is specified as a column that makes an external reference

The following is an example:
```
SELECT "C1" FROM (SELECT COUNT("C1") AS "C1" FROM "T1") AS "V1"
    GROUP BY "C1"
    HAVING EXISTS(SELECT * FROM "T2" WHERE MAX("V1"."C1")="C1")
```

If a derived query name specifying a window function is specified in a FROM clause in the outermost query specification of a derived query

The following is an example:

SELECT "C2" FROM (SELECT "C1",AVG("C1") OVER(ORDER BY "C2") AS "C2"
                      FROM "T1") AS "V1"

The LIMIT clause is included in the outermost query specification of a derived query.

The following is an example:

SELECT "C1","C2" FROM (SELECT "C1","C2" FROM "T1" LIMIT 10) AS "V1"

A derived query name for which a comma join is specified is specified for a table reference to a joined table in the outermost query specification of a derived query.

The following is an example:

SELECT "VC1","VC2" FROM (SELECT "T1"."C1","T2"."C1" FROM "T1","T2","T3"
                           WHERE "T1"."C1"="T2"."C1"
                             AND "T2"."C1"="T3"."C1") AS "V1"("VC1","VC2")
                         LEFT JOIN "T3" ON "VC1" = "T3"."C1"

A derived query is specified for a table reference in FULL OUTER JOIN.

The following is an example:

SELECT * FROM (SELECT "C1" FROM "T1") AS "V1"
                 FULL OUTER JOIN "T2" ON "V1"."C1"="T2"."C1"

VARCHAR-type data larger than 32,000 bytes is contained in the column derived as a result of a set operation that uses an operand that is the outermost query specification of a derived query.

The following is an example:

SELECT "C1" FROM (SELECT "C1" FROM "T1"
                  UNION
                  SELECT "DEFINE_SOURCE" FROM "MASTER"."SQL_DEFINE_SOURCE"
                 ) AS "V1"

At least one of the conditions under which an internal derived table in preceding items 1 to 18 is not expanded is satisfied in the following case: the query specification in an operand of a set operation specified in a derived query is assumed to be a derived query.

The following is an example:

SELECT "C1" FROM (SELECT DISTINCT "C1","C2" FROM "T1"
                  UNION ALL
                  SELECT "C1","C2" FROM "T2"
                 ) AS "V1"

Assume that SELECT DISTINCT "C1","C2" FROM "T1", which is a query specification in an operand of a set operation, is a derived query. In this case, a condition^# under which the internal derived table in item 1 is not expanded is satisfied. Therefore, derived table V1 is not expanded.

#

The FROM clause includes the name of a derived query in which SELECT DISTINCT is specified in the outermost query specification, and the FROM clause is directly included in a query specification that satisfies the following condition:

A derived column derived from a value expression that includes a column specification is not specified as a single column specification in a selection expression.

If, in the FROM clause of the outermost query specification for a derived query, you specify a derived query name that specifies only the UNION ALL set operator, and one of the following is specified in the query specification that directly contains that FROM clause:

GROUP BY clause, HAVING clause, or set function

The following is an example:

SELECT "C1","C2"
    FROM (
          SELECT "C1","C2" FROM "T1"
          UNION ALL SELECT "C1","C2" FROM "T2"
         ) AS "V1"
    GROUP BY "C1","C2"

Window function specified in the selection expression

The following is an example:

SELECT "C1",SUM("C1") OVER(ORDER BY "C2")
    FROM (
          SELECT "C1","C2" FROM "T1"
          UNION ALL SELECT "C1","C2" FROM "T2"
          ) AS "V1"

Table join (including joined table)

The following is an example:

SELECT * FROM (
               SELECT "C1", "C2" FROM "T1"
               UNION ALL SELECT "C1","C2" FROM "T2"
               ) AS "V1","T3"

Sort key that is a derived query name column that is not specified in the selection expression

The following is an example:

SELECT "C1" FROM (
                  SELECT "C1","C2" FROM "T1"
                  UNION ALL SELECT "C1","C2" FROM "T2"
                  ) AS "V1"
    ORDER BY "C2"

Sort key value expression that is not specified as a selection expression

The following is an example:

SELECT "C1" FROM (
                  SELECT "C1","C2" FROM "T1"
                  UNION ALL SELECT "C1","C2" FROM "T2"
                  ) AS "V1"
    ORDER BY ("C1"+1)

If, in the FROM clause of the outermost query specification for a derived query, you specify a derived query name that specifies a set operator other than UNION ALL, and one of the following is specified in the query specification that directly contains that FROM clause:

SELECT DISTINCT

The following is an example:

SELECT DISTINCT "C1" FROM (
                           SELECT "C1" FROM "T1"
                           EXCEPT ALL SELECT "C1" FROM "T2"
                           ) AS "V1"

GROUP BY clause, HAVING clause, or set function

The following is an example:

SELECT "C1","C2" FROM (
                       SELECT "C1","C2" FROM "T1"
                       UNION SELECT "C1","C2" FROM "T2"
                       ) AS "V1"
    GROUP BY "C1","C2"

Window function specified in the selection expression

The following is an example:

SELECT "C1",SUM("C1") OVER(ORDER BY "C2") FROM (
                      SELECT "C1","C2" FROM "T1"
                      INTERSECT ALL SELECT "C1","C2" FROM "T2"
                      ) AS "V1"

Table join (including joined table)

The following is an example:

SELECT * FROM (
               SELECT "C1","C2" FROM "T1"
               EXCEPT SELECT "C1","C2" FROM "T2"
               ) AS "V1","T3"

Selection expression containing a value expression that includes scalar function RANDOM, scalar function RANDOM_NORMAL, scalar function RANDOMROW, a scalar subquery, or a window function

The following is an example:

SELECT "VC1","VC2",RANDOM() FROM (
                                  SELECT "C1","C2" FROM "T1"
                                  UNION SELECT "C1","C2" FROM "T2"
                                  ) AS "V1"("VC1","VC2")

Selection expression containing one or more derived query name columns that are not specified as single column specifications

The following is an example:

SELECT "VC1" FROM (
                   SELECT "C1","C2" FROM "T1"
                   INTERSECT SELECT "C1","C2" FROM "T2"
                   ) AS "V1"("VC1","VC2")

Because derived column VC2 is not specified in the selection expression, derived table V1 is not expanded.

The following is an example:

SELECT "VC1"*1.05,"VC2" FROM (
                              SELECT "C1","C2" FROM "T1"
                              UNION SELECT "C1","C2" FROM "T2"
                              ) AS "V1"("VC1","VC2")

Because derived column VC1 is not specified as a single column specification in the selection expression, derived table V1 is not expanded.

Sort key that is a derived query name column that is not specified in the selection expression

The following is an example:

SELECT "C1" FROM (
                  SELECT "C1","C2" FROM "T1"
                  UNION SELECT "C1","C2" FROM "T2"
                  ) AS "V1"
    ORDER BY "C2"

Sort key value expression that is not specified as a selection expression

The following is an example:

SELECT "C1" FROM (
                  SELECT "C1","C2" FROM "T1"
                  UNION SELECT "C1","C2" FROM "T2"
                  ) AS "V1"
    ORDER BY ("C1"+1)