Example: Using CASE_N in a List of Partitioning Expressions that Define a Multilevel PPI - Advanced SQL Engine - Teradata Database

SQL Functions, Expressions, and Predicates

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-28
dita:mapPath
SQL_Functions__Expressions__and_Predicates.Upload_071421/djk1612415574830.ditamap
dita:ditavalPath
SQL_Functions__Expressions__and_Predicates.Upload_071421/wrg1590696035526.ditaval
dita:id
B035-1145
lifecycle
previous
Product Category
Teradata Vantage™

Here is an example that modifies “Example: Defining the Partition to Which a Row is Assigned” to use CASE_N in a list of partitioning expressions that define a multilevel PPI:

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

The example defines six partitions to which a row can be assigned. The first CASE_N expression defines three partitions based on the value of the totalorders column. The second CASE_N expression subdivides each of the three partitions into two partitions based on the value of the orderdate column.

Level 1 Partition Number Level 2 Partition Number Condition
1 1 The value of the totalorders column is less than 100 and the value of the orderdate column is less than or equal to '2005-12-31'.
2 The value of the totalorders column is less than 100 and the value of the orderdate column is greater than '2005-12-31'.
2 1 The value of the totalorders column is less than 1000 but greater than or equal to 100, and the value of the orderdate column is less than or equal to '2005-12-31'.
2 The value of the totalorders column is less than 1000 but greater than or equal to 100, and the value of the orderdate column is greater than '2005-12-31'.
3 1 The value of the totalorders column is greater than or equal to 1000 and the value of the orderdate column is less than or equal to '2005-12-31'.
2 The value of the totalorders column is greater than or equal to 1000 and the value of the orderdate column is greater than '2005-12-31'.