Specifying a Column PARTITION or PARTITION#L n - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantage™

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.