Usage Notes - Teradata Database

SQL Data Manipulation Language

Product
Teradata Database
Release Number
15.10
Language
English (United States)
Last Update
2018-10-06
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata® Database

HAVING Clause

The rules and restrictions are:

  • You cannot specify LOB columns in the HAVING search condition.
  • The conditional expression can define one or more aggregates (for example, MAX, MIN, AVG, SUM, COUNT) and can be applied to the rows of the following group conditions:
  • A single group defined in the SELECT expression list, which has only aggregate results
  • One or more groups defined in a GROUP BY clause
  • HAVING cannot be specified in a recursive statement of a recursive query; however, a nonrecursive seed statement in a recursive query can specify the HAVING clause.
  • If you specify the value for a row‑level security constraint in a search condition, that value must be expressed in its encoded form.
  • You can specify a scalar subquery as an operand of a scalar predicate in the HAVING clause of a query.
  • The following rules apply to the use of the DEFAULT function as part of the search condition within a HAVING clause:
  • You can specify a DEFAULT function with a column name argument within a predicate. The system evaluates the DEFAULT function to the default value of the column specified as its argument. Once the system has evaluated the DEFAULT function, it treats it like a constant in the predicate.
  • You can specify a DEFAULT function without a column name argument within a predicate only if there is one column specification and one DEFAULT function as the terms on each side of the comparison operator within the expression.
  • Following existing comparison rules, a condition with a DEFAULT function used with comparison operators other than IS NULL or IS NOT NULL is unknown if the DEFAULT function evaluates to null.
  • A condition other than IS NULL or IS NOT NULL with a DEFAULT function compared with a null evaluates to unknown.

     

    IF a DEFAULT function is used with …

    THEN the comparison is …

    IS NULL

  • TRUE if the default is null
  • Else it is FALSE
  • IS NOT NULL

  • FALSE if the default is null
  • Else it is TRUE
  • See SQL Functions, Operators, Expressions, and Predicates for more information about the DEFAULT function.

  • You cannot specify a SAMPLE clause in a subquery used as a HAVING clause predicate.