16.20 - Joins and Aggregation in View Definitions - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Detailed Topics

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Release Date
March 2019
Content Type
Programming Reference
Publication ID
B035-1184-162K
Language
English (United States)

When defining a non-updatable view, you can specify one or more aggregate operators (SUM, AVG, MAX, MIN, and COUNT) in the SELECT expression list or in the conditional expression of a HAVING clause. Creating views with aggregation can eliminate the need to create and delete temporary tables. For many applications, an aggregate join index might be a more optimal solution. For more information about aggregate join indexes, see Aggregate Join Indexes, Optimizer Rules for Using Aggregate Join Indexes in a Query Plan, and “Join and Hash Indexes” in Teradata Vantage™ - Database Design, B035-1094.

Aggregate operators cannot be used in the conditional expression of a WHERE clause.