CASE_N
Purpose
Evaluates a list of conditions and returns the position of the first condition that evaluates to TRUE, provided that no prior condition in the list evaluates to UNKNOWN.
Syntax
where:
|
Syntax element … |
Specifies … |
|
conditional_expression |
a conditional expression or comma-separated list of condition expressions to evaluate. A conditional expression must evaluate to TRUE, FALSE, or UNKNOWN. |
|
NO CASE |
an optional condition that evaluates to TRUE if every conditional_expression in the list evaluates to FALSE. |
|
OR UNKNOWN |
an optional condition to use with NO CASE. The NO CASE OR UNKNOWN condition evaluates to TRUE if every conditional_expression in the list evaluates to FALSE, or if a conditional_expression evaluates to UNKNOWN and all prior conditions in the list evaluate to FALSE. |
|
UNKNOWN |
an optional condition that evaluates to TRUE if a conditional_expression evaluates to UNKNOWN and all prior conditions in the list evaluate to FALSE. |
ANSI Compliance
This is a Teradata extension to the ANSI SQL:2011 standard.
Evaluation
CASE_N evaluates conditional_expressions from left to right until a condition evaluates to TRUE or UNKNOWN, or until every condition evaluates to FALSE. The position of the first conditional_expression is one and the positions of subsequent conditions increment by one up to n, where n is the total number of conditional expressions.
|
IF … |
THEN … |
|||
|
a conditional_expression evaluates to TRUE, and all prior conditions evaluate to FALSE |
CASE_N returns the position of the conditional_expression. |
|||
|
a conditional_expression evaluates to UNKNOWN, and all prior conditions evaluate to FALSE |
|
|||
|
If NO CASE OR UNKNOWN is specified, CASE_N returns n + 1. If UNKNOWN is specified and NO CASE is not specified, CASE_N returns n + 1. If NO CASE and UNKNOWN are specified, CASE_N returns n + 2. If neither UNKNOWN nor NO CASE OR UNKNOWN is specified, CASE_N returns NULL. |
||||
|
every conditional_expression evaluates to FALSE |
|
|||
|
If NO CASE or NO CASE OR UNKNOWN is specified, CASE_N returns n + 1. If neither NO CASE nor NO CASE OR UNKNOWN is specified, CASE_N returns NULL |
||||
Result Type and Attributes
The data type, format, and title for CASE_N are as follows.
|
Data Type |
Format |
Title |
|
INTEGER |
Default format for INTEGER |
<CASE_N function> |
For information on default data type formats, see SQL Data Types and Literals.
Using CASE_N to Define Partitioned Primary Indexes
The primary index for a table or join index controls the distribution and retrieval of the data for that table or join index across the AMPs. If the primary index is a partitioned primary index (PPI), the data can be assigned to user-defined partitions on the AMPs.
To define a primary index for a table or join index, you specify the PRIMARY INDEX phrase in the CREATE TABLE or CREATE JOIN INDEX data definition statement. To define a partitioned primary index, you include the PARTITION BY phrase when you define the primary index.
The PARTITION BY phrase requires one or more partitioning expressions that determine the partition assignment of a row. You can use CASE_N to construct a partitioning expression such that a row with any value or NULL for the partitioning columns is assigned to some partition.
You can also use RANGE_N to construct a partitioning expression. For more information, see “RANGE_N” on page 153.
If the PARTITION BY phrase specifies a list of partitioning expressions, the PPI is a multilevel PPI, where each partition for a level is subpartitioned according to the next partitioning expression in the list. Unlike the partitioning expression for a single-level PPI, which can consist of any valid SQL expression (with some exceptions), each expression in the list of partitioning expressions for a multilevel PPI must be a CASE_N or RANGE_N function.
You cannot ADD or DROP partitioning expressions that are based on a CASE_N function. To modify a partitioning expression that is based on a CASE_N function, you must use the ALTER TABLE statement with the MODIFY PRIMARY INDEX option to redefine the entire PARTITION BY clause, and the table must be empty. For more information, see “ALTER TABLE” in SQL Data Definition Language.
Using CASE_N with CURRENT_DATE or CURRENT_TIMESTAMP in a PPI
You can define a partitioning expression that uses CASE_N with the built-in functions CURRENT_DATE or CURRENT_TIMESTAMP. Subsequently, you can use the ALTER TABLE TO CURRENT statement to re-partition the table data using a newly resolved current date or timestamp. For more information, see “Rules and Guidelines for Optimizing the Reconciliation of CASE_N PPI Expressions Based On Updatable Current Date and Updatable Current Timestamp” in SQL Data Definition Language Detailed Topics.
Using CASE_N with Character Comparison
You can specify conditional expressions in the CASE_N function that compare CHAR, VARCHAR, GRAPHIC or VARGRAPHIC data types. The following usage rules apply:
If not explicitly specified, the default of NOT CASESPECIFIC is used if Teradata session transaction semantics are in effect. If ANSI session transaction semantics are in effect, the default is CASESPECIFIC.
For example, if a conditional expression is a combination of NOT CASESPECIFIC expressions and a literal with no case specific qualifier (CASESPECIFIC, NOT CASESPECIFIC), the case specificity will be case specific in ANSI mode sessions and not case specific in Teradata mode sessions.
Note: All character string comparisons involving graphic data are case specific.
Note that the pad character might not collate to the lowest code point in the collation. For a literal of length n, if the column value being compared precisely matches the literal for the first n characters, but contains a character that collates less than the pad character at position n+1, then the column value will collate less than the string literal. See “Example 9” on page 132.
Restrictions
If CASE_N is used in a PARTITION BY phrase, it:
If CASE_N is used in a partitioning expression for a multilevel PPI, it must define at least two partitions.
Note that partition elimination for queries is often limited to literal or using value equality conditions on the partitioning columns, and the Optimizer may not eliminate some partitions when it possibly could. Also, evaluating a complex CASE_N may be costly in terms of CPU cycles and the overhead of CASE_N may cause the table header to be excessively large.
Example
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. |
Example
Here is an example that modifies “Example 1” 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'. |
Example
The following example shows the count of rows in each partition if the orders table were to be partitioned using the CASE_N expression.
CREATE TABLE orders
(orderkey INTEGER NOT NULL
,custkey INTEGER
,orderdate DATE FORMAT 'yyyy-mm-dd' NOT NULL)
PRIMARY INDEX (orderkey);
INSERT INTO orders (1, 1, '1996-01-01');
INSERT INTO orders (2, 1, '1997-04-01');
The CASE_N expression in the following SELECT statement specifies three conditional expressions and the NO CASE condition.
SELECT COUNT(*),
CASE_N(orderdate >= '1996-01-01' AND
orderdate <= '1996-12-31' AND
custkey <> 999999,
orderdate >= '1997-01-01' AND
orderdate <= '1997-12-31' AND
custkey <> 999999,
orderdate >= '1998-01-01' AND
orderdate <= '1998-12-31' AND
custkey <> 999999,
NO CASE
) AS Partition_Number
FROM orders
GROUP BY Partition_Number
ORDER BY Partition_Number;
The results look like this:
Count(*) Partition_Number
----------- ----------------
1 1
1 2
Example
The following example creates a table partitioned with orders data for each quarter in 2008.
CREATE TABLE Orders
(O_orderkey INTEGER NOT NULL,
O_custkey INTEGER,
O_orderperiod PERIOD (DATE) NOT NULL,
O_orderpriority CHAR (21),
O_comment VARCHAR (79))
PRIMARY INDEX (O_orderkey)
PARTITION BY
CASE_N (END (O_orderperiod) <= date'2008-03-31', /* First Quarter */
END (O_orderperiod) <= date'2008-06-30', /* Second Quarter */
END (O_orderperiod) <= date'2008-09-30', /* Third Quarter */
END (O_orderperiod) <= date'2008-12-31' /* Fourth Quarter */
);
The following SELECT statement scans two partitions and displays the details of the orders placed for the first two quarters.
SELECT *
FROM Orders
WHERE END (O_orderperiod) > date'2008-06-30';
Example
The following example uses IS [NOT] UNTIL_CHANGED in the PPI partitioning expression to check whether or not the ending bound of a Period expression is UNTIL_CHANGED.
CREATE TABLE TESTUC
(A INTEGER,
B PERIOD (DATE),
C INTEGER)
PRIMARY INDEX (A)
PARTITION BY
CASE_N (END (b) IS UNTIL_CHANGED,
END (b) IS NOT UNTIL_CHANGED, UNKNOWN);
Example
The following example uses IS [NOT] UNTIL_CLOSED in the PPI partitioning expression to check whether or not the ending bound of a transaction time column is UNTIL_CLOSED.
CREATE TABLE TESTUCL
(A INTEGER,
B PERIOD (TIMESTAMP (6) WITH TIME ZONE) NOT NULL AS TRANSACTIONTIME,
C INTEGER)
PRIMARY INDEX (A)
PARTITION BY
CASE_N (END (b) IS UNTIL_CLOSED,
END (b) IS NOT UNTIL_CLOSED, UNKNOWN);
Example
In this example, the session collation is ASCII.
CASE_N (a<'b', a>='ba' and a<'dogg' and b<>'cow', c<>'boy', NO CASE OR UNKNOWN)
The following table shows the result value returned by the above CASE_N function given the specified values for a, b, and c. x and y represent any value or NULL. The value 4 is returned when all the conditions are FALSE, or a condition is UNKNOWN with all preceding conditions evaluating to FALSE.
|
a |
b |
c |
Result |
|
'a' |
x |
y |
1 |
|
'boy' |
'girl' |
y |
2 |
|
'boy' |
NULL |
y |
4 |
|
'boy' |
'cow' |
'man' |
3 |
|
'boy' |
'cow' |
'boy' |
4 |
|
'dog' |
'ball' |
y |
2 |
|
'dogg' |
x |
NULL |
4 |
|
'dogg' |
x |
'man' |
3 |
|
'egg' |
x |
'boy' |
4 |
|
'egg' |
x |
NULL |
4 |
|
'egg' |
x |
'girl' |
3 |
Example
In this example, the session collation is ASCII.
CASE_N (a<'b', a>='ba' and a<'dogg' and b<>'cow', c<>'boy', UNKNOWN)
The following table shows the result value returned by the above CASE_N function given the specified values for a, b, and c. The x and y represent any value or NULL. The value 4 is returned if a condition is UNKNOWN with all preceding conditions evaluating to FALSE. NULL is returned if all the conditions are false.
|
a |
b |
c |
Result |
|
'a' |
x |
y |
1 |
|
'boy' |
'girl' |
y |
2 |
|
'boy' |
NULL |
y |
4 |
|
'boy' |
'cow' |
'man' |
3 |
|
'boy' |
'cow' |
'boy' |
NULL |
|
'dog' |
'ball' |
y |
2 |
|
'dogg' |
x |
NULL |
4 |
|
'dogg' |
x |
'man' |
3 |
|
'egg' |
NULL |
'boy' |
NULL |
|
'egg' |
x |
'boy' |
NULL |
|
'egg' |
x |
NULL |
4 |
|
'egg' |
x |
'girl' |
3 |
Example
In this example, the session collation is ASCII when submitting the CREATE TABLE statement, and the pad character is <space>. The example defines two partitions (numbered 1 and 2) based on the value of a:
CREATE SET TABLE t2
(a VARCHAR(10) CHARACTER SET UNICODE NOT CASESPECIFIC,
b INTEGER)
PRIMARY INDEX (a)
PARTITION BY CASE_N(a BETWEEN 'a' AND 'b', a BETWEEN 'b' AND 'c');
The following INSERT statement inserts a character string consisting of a single <tab> character between the 'b' and '1'.
INSERT t2 ('b 1', 1);
The following INSERT statement inserts a character string consisting of a single <space> character between the 'b' and '1'.
INSERT t2 ('b 1', 2);
The following SELECT statement shows the result of the INSERT statements. Since the <tab> character has a lower code point than the <space> character, the first string inserted maps to partition 1.
SELECT PARTITION, a, b FROM t2 ORDER BY 1;
*** Query completed. 2 rows found. 3 columns returned.
*** Total elapsed time was 1 second.
PARTITION a b
----------- ------ -----
1 b 1 1 (string contains single <tab> character)
2 b 1 2 (string contains single <space> character)
Related Topics
|
For information on … |
See … |
|
PPI properties and performance considerations |
Database Design. |
|
PPI considerations and capacity planning |
|
|
the specification of a PPI for a table |
CREATE TABLE in SQL Data Definition Language. |
|
the specification of a PPI for a join index |
CREATE JOIN INDEX in SQL Data Definition Language. |
|
the modification of the partitioning of the primary index for a table |
ALTER TABLE in SQL Data Definition Language. |
|
the reconciliation of the partitioning based on newly resolved CURRENT_DATE and CURRENT_TIMESTAMP values |
ALTER TABLE TO CURRENT in SQL Data Definition Language |