17.05 - Specifying a Column PARTITION or PARTITION#L n - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Advanced SQL Engine
Teradata Database
Release Number
January 2021
English (United States)
Last Update

You can specify a system-derived column PARTITION or PARTITION#L n (n between 1 and 62) in a WHERE clause if the referenced table does not have a user-defined column named partition or PARTITION#L n, respectively. You can specify these system-derived columns for a table that does not have partitioning, but the value returned for such a system-derived column is always 0, so the only reason to do so is to determine if a nonempty table is partitioned.

PARTITION is equivalent to a value expression where the expression is identical to the combined partitioning expression defined for the table with column references appropriately qualified as needed.

PARTITION#L n, where n ranges from 1 to 62, inclusive, is equivalent to a value expression where the expression is identical to the partitioning expression at the corresponding level (or 1 if this is a column partitioning level), or zero if the table is not partitioned.

Therefore, a query made on a partitioned table that specifies the predicate WHERE PARTITION <> combined_partitioning_expression should always return 0 rows. If any rows are returned, then they are not partitioned properly, and the table should be revalidated immediately.