Example: Defining the Partition to Which a Row is Assigned

Teradata Vantageā„¢ SQL Functions, Expressions, and Predicates

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1145-162K

Here is an example that uses CASE_N and the value of the totalorders column to define the partition to which a row is assigned:

   CREATE TABLE orders 
    (storeid INTEGER NOT NULL
    ,productid INTEGER NOT NULL
    ,orderdate DATE FORMAT 'yyyy-mm-dd' NOT NULL
    ,totalorders INTEGER)
    PRIMARY INDEX (storeid, productid) 
     PARTITION BY CASE_N(totalorders < 100, totalorders < 1000,
                         NO CASE, UNKNOWN);

In the example, CASE_N specifies four partitions to which a row can be assigned, based on the value of the totalorders column.

Partition Number Condition
1 The value of the totalorders column is less than 100.
2 The value of the totalorders column is less than 1000, but greater than or equal to 100.
3 The value of the totalorders column is greater than or equal to 1000.
4 The totalorders column is NULL.