5.11.6 Equivalent exchange for a HAVING clause (converting to the WHERE clauses)

The search condition in a HAVING clause might be converted to a search condition in the WHERE clause. This equivalent exchange might enable unnecessary input rows to be deleted during grouping and indexes to be used for table retrieval processing.

The formats of conditions that are converted to a search condition in the WHERE clause are shown below. If equivalent exchange occurs for a search condition, the indexes to be used during retrieval processing are determined on the basis of the search condition obtained after the equivalent exchange.

Format of conditions 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

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
```

Notes

Conditions specified in an OR condition of a logical operation are not subject to this equivalent exchange.

However, if the following equivalent exchange is applied to the OR condition of the logical operation , after the following equivalent exchange is applied, equivalent exchange related to the HAVING clause will take place.

• 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 outside the OR condition

For details about equivalent exchange for the OR condition, 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 in logical operations and conditions containing subqueries are not subject to this equivalent exchange.
If column-specification columns are external reference columns, this equivalent exchange is not applied. 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 scalar operations equivalent to literals, see the table Conditions under which value expressions are equivalent to literals under Rules in Specification format and rules for value expressions in the manual HADB SQL Reference.

To Page Top