17.05 - Rules and Restrictions for the QUALIFY Clause - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)
The rules and restrictions are:
  • You cannot specify LOB columns in a QUALIFY clause search condition.
  • You cannot specify an OR condition connected with a subquery in a QUALIFY clause search condition.

    The following query fails because of the OR condition connected with the subquery.

         SELECT RANK (column_1)
         FROM table_1
         QUALIFY column_1 IN (SELECT table_2.column_1
                              FROM table_2)
         OR table_1.column.1 = 10000;
  • When you specify a QUALIFY clause in a query, you must also specify a statistical function in one of the following locations within the query.
    • The select list of the SELECT clause
    • The grouping key of the GROUP BY clause
    • The search condition of the QUALIFY 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.
  • The following rules apply to the use of the DEFAULT function as part of the search condition within a QUALIFY clause.
    • You can specify a DEFAULT function with a column name argument within a predicate. The database evaluates the DEFAULT function to the default value of the column specified as its argument and uses the value 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 Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145 for more information about the DEFAULT function.

  • A SELECT statement that specifies the TOP n operator cannot also specify the QUALIFY clause.
  • You cannot specify a SAMPLE clause in a subquery used as a QUALIFY clause predicate.