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.