5.11.7 Equivalent exchange for search conditions in SQL statements that specify derived queries (transposition to the WHERE clause of a derived query)

(1) Examples where equivalent exchange is performed

To Page Top

(2) Format of conditions subject to equivalent exchange

The following shows the format of conditions that are subject to equivalent exchange:

• Comparison predicate

  ▪ column-specification comparison-operator {literal|dynamic-parameter|datetime-information-acquisition-function|user-information-acquisition-function}
  ▪ {literal|dynamic-parameter|datetime-information-acquisition-function|user-information-acquisition-function} comparison-operator column-specification
  ▪ CONTAINS (column-specification,search-condition-expression-string) > 0

• BETWEEN predicate

  column-specification [NOT] BETWEEN {literal|dynamic-parameter|datetime-information-acquisition-function|user-information-acquisition-function}
                     AND {literal|dynamic-parameter|datetime-information-acquisition-function|user-information-acquisition-function}

• IN predicate

  column-specification [NOT] IN ({literal|dynamic-parameter|datetime-information-acquisition-function|user-information-acquisition-function}
                  [,{literal|dynamic-parameter|datetime-information-acquisition-function|user-information-acquisition-function}]...)

• LIKE predicate

  column-specification [NOT] LIKE pattern-character-string [ESCAPE escape-character]
    pattern-character-string::={literal|dynamic-parameter|datetime-information-acquisition-function|user-information-acquisition-function}
    escape-character::={literal|dynamic-parameter}

• LIKE_REGEX predicate

  column-specification [NOT] LIKE_REGEX regular-expression-character-string [FLAG {I|IGNORECASE}]
    regular-expression-character-string::=literal

• NULL predicate

  column-specification IS [NOT] NULL

To Page Top

(3) Notes

• Equivalent exchange will be performed if the selection expression of the derived query on which the derived column specified in the column specification of the search condition is based is a column specification.

• Equivalent exchange is not performed if a window function is specified in the derived query.

• Equivalent exchange is not performed if the derived query is expanded into the SQL statement that specifies the derived query. For details about the expansion of derived queries, see Internal derived tables in the manual HADB SQL Reference.

• Equivalent exchange is not performed for the following types of derived queries:

  • The derived query specifies the name of a viewed table, and the same viewed table name is specified more than once in the SQL statement.

  • The derived query specifies the name of a query specified as a WITH list element, and the same query name is specified more than once in the SQL statement.

  • Multiple WITH list elements are specified, and the derived query specifies a query name that is already specified as a WITH list element.

  • The derived query is specified in a joined table other than a joined table for which only INNER JOIN is specified its join type.

  • A table value constructor is specified as the derived query.

  • The derived query is a recursive query.

• When all of the following conditions are satisfied, equivalent exchange is applied to the search condition in the SQL statement that specifies the derived query:

  • A condition specified as an OR condition in a logical operation meets the format requirements for a search condition that is subject to equivalent exchange for search conditions in SQL statements that specify derived queries

  • All column specifications point to columns in the same derived table

• When applying the following equivalent exchange to the OR condition of a logical operation, the following equivalent exchange is applied first. Then, equivalent exchange for search conditions in SQL statements that specify derived queries is applied.

  • Equivalent exchange that moves a condition from inside the OR condition to outside the OR condition

  • Equivalent exchange that adds the IN condition created from an = condition in the OR condition to the outside of the OR condition

  For details about equivalent exchange described earlier, see 5.11.1 Equivalent exchange for OR conditions (removing from the OR conditions) and 5.11.2 Equivalent exchange for OR conditions (converting to IN conditions).

• Conditions specified in NOT conditions in logical operations and conditions that contain subqueries are not subject to equivalent exchange.

• Equivalent exchange is not performed if the columns specified by column-specification are external reference columns. For details about external reference columns, see Specification format and rules for subqueries in the manual HADB SQL Reference.

• If only literals are specified in a scalar operation, that scalar operation might be treated as a literal. For details about the conditions under which scalar operations are equivalent to literals, see Specification format and rules for value expressions in the manual HADB SQL Reference.

To Page Top