Interview Questions   Tutorials   Discussions   Programs

# Teradata - What are CASE_N and RANGE_N PPI

663
views
asked vishnoiprem August 17, 2014 05:23 AM

What are CASE_N and RANGE_N PPI

```1) CREATE TABLE Order_Table ( Order_Number Integer NOT NULL ,Customer_Number Integer NOT NULL ,Order_Date Date ,Order_Total Decimal (10,2) ) PRIMARY INDEX(Customer_Number) PARTITION BY CASE_N (Order_Total < 1000 ,Order_Total < 5000 ,Order_Total < 10000 ,Order_Total < 50000, NO Case, Unknown ); 2) CREATE TABLE Order_Table ( Order_Number Integer NOT NULL ,Customer_Number Integer NOT NULL ,Order_Date Date ,Order_Total Decimal (10,2) )UNIQUE PRIMARY INDEX (Customer_Number, Order_Date) PARTITION BY Range_N (Order_Date BETWEEN DATE 2003-01-01 AND 2003-06-30 EACH INTERVAL 1 DAY );```
Teradata now allows you to *****1 the CASE statement as a partitioning option. Here are
the fundamentals:
Use of CASE_N results in:

 Just like the CASE statement it evaluates a list of conditions picking only the first
condition met.
 The data row will be placed into a partition associated with that condition.

Partitioning with RANGE_N
Teradata also has a western theme because they allow your partitions to go Home on the
Range by using the RANGE_N function. Here are the fundamentals. Use of
RANGE_N results in:
 The expression is evaluated and associated to one of a list of ranges.
 Ranges are always listed in increasing order and cant overlap
 The data row is placed into the partition that falls within the associated range.
 The test value in RANGE_N function must be an INTEGER or DATE. (This
includes BYTEINT or SMALLINT)
In the example below please notice the arrows. They are designed to illustrate that you
can use a UNIQUE PRIMARY INDEX

flag

### Join with account you already have

Preview

Online-Classroom Classes
www.writeabc.com

1 person following this question

1 person following this tag

Question tags