CASE_N Function Examples | Teradata Vantage - Example: Defining the Partition to Which a Row is Assigned - 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 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.