17.10 - WHERE search_condition - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1144-171K
Language
English (United States)

The WHERE clause is useful for creating sparse join indexes.

For more information about the WHERE clause, see Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.

search_condition
A conditional expression to eliminate all rows from the SELECT clause query that do not evaluate to TRUE.
You can base an expression on a UDT column or an expression that references at least one column with the exception of the following: expressions that involve aggregate or OLAP functions, UDF expressions, and built-in functions or keywords that are explicitly prohibited such as DEFAULT or PARTITION. For information about the DEFAULT built-in function, see Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145. For information about the PARTITION keyword, see Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.
You must alias expressions. Otherwise, an error is returned.
Multiple join conditions must be connected using the AND logical operator.
Data types for any columns used in a join condition must be drawn from the same domain because neither explicit nor implicit data type conversions are permitted.
You cannot specify the ROWID keyword in the search condition of a sparse join index definition.
You must drop any join indexes created prior to Vantage V2R6.2 that contain the keyword ROWID in their definition, then recreate them using currently valid syntax.
If you specify a join condition on a UDT column, the specified tables must be joined on an equality condition. Otherwise, Vantage returns an error to the requestor.
The following rules apply to specifying inequality operators in a WHERE clause search condition:
  • Inequality conditions between columns from the same table are always valid.
  • Inequality conditions between columns from different tables are supported only if they are ANDed to at least one equality condition.
  • Inequality conditions can be specified only for columns having the same data type in order to enforce domain integrity.
  • You can only specify a join condition using an inequality operator if you specify multiple join conditions and use the AND logical operator to connect the inequality join condition or conditions with an equality join condition.
  • You can specify an equality or inequality join condition between two tables based on an expression if you specify multiple join conditions between the two tables and use the AND logical operator to connect the join condition that is specified using an expression or columnar expressions with an equality join condition between columns from the two tables.
  • The only valid comparison operators for an inequality condition are the following.

    <

    <=

    >

    >=