Valued CASE Expression
Purpose
Evaluates a set of expressions for equality with a test expression and returns as its result the value of the scalar expression defined for the first WHEN clause whose value equals that of the test expression. If no equality is found, then CASE returns the scalar value defined by an optional ELSE clause, or if omitted, NULL.
Syntax
where:
|
Syntax element … |
Specifies … |
|
value_expression_1 |
an expression whose value is tested for equality with value_expression_n. |
|
value_expression_n |
a set of expressions against which the value for value_expression_1 is tested for equality. |
|
scalar_expression_n |
an expression whose value is returned on the first equality comparison of value_expression_1 and value_expression_n. |
|
scalar_expression_m |
an expression whose value is returned if evaluation falls through to the ELSE clause. |
ANSI Compliance
This is ANSI SQL:2011 compliant.
Teradata Database does not enforce the ANSI restriction that value_expression_1 must be a deterministic function. In particular, Teradata Database allows the function RANDOM to be used in value_expression_1.
Note that if RANDOM is used, nondeterministic behavior may occur, depending on whether value_expression_1 is recalculated for each comparison to value_expression_n.
Usage Notes
WHEN clauses are processed sequentially.
The first WHEN clause value_expression_n that equates to value_expression_1 returns the value of its associated scalar_expression_n as its result. The evaluation process then terminates.
If no value_expression_n equals value_expression_1, then scalar_expression_m, the argument of the ELSE clause, is the result.
If no ELSE clause is defined, then the result defaults to NULL.
The data type of value_expression_1 must be comparable with the data types of all of the value_expression_n values.
For information on the result data type of a CASE expression, see “Rules for the CASE Expression Result Type” on page 570.
You can use a scalar subquery in the WHEN clause, THEN clause, and ELSE clause of a CASE expression. If you use a non-scalar subquery (a subquery that returns more than one row), a runtime error is returned.
Recommendation: Do not use the built-in functions CURRENT_DATE or CURRENT_TIMESTAMP in a CASE expression that is specified in a partitioning expression for a partitioned primary index (PPI). In this case, all rows are scanned during reconciliation.
Default Title
The default title for a CASE expression appears as:
<CASE expression>
Restrictions on the Data Types in a CASE Expression
The following restrictions apply to CLOB, BLOB, and UDT types in a CASE expression:
|
Data Type |
Restrictions |
|
BLOB |
A BLOB can only appear in value_expression_1, value_expression_n, scalar_expression_m, or scalar_expression_n when it is cast to BYTE or VARBYTE. |
|
CLOB |
A CLOB can only appear in value_expression_1, value_expression_n, scalar_expression_m, or scalar_expression_n when it is cast to CHAR or VARCHAR. |
|
UDT |
Multiple UDTs can appear in a CASE expression, with the following restrictions: Teradata Database does not perform implicit type conversion on UDTs in CASE expressions. A workaround for this restriction is to use CREATE CAST to define casts that cast between the UDTs, and then explicitly invoke the CAST function in the CASE expression. For more information on CREATE CAST, see SQL Data Definition Language. |
Related Topics
|
For additional notes on … |
See … |
|
error conditions |
|
|
the result data type of a CASE expression |
|
|
format of the result of a CASE expression |
|
|
nulls and CASE expressions |
Example
The following example uses a Valued CASE expression to calculate the fraction of cost in the total cost of inventory represented by parts of type ‘1’:
SELECT SUM(CASE part
WHEN '1'
THEN cost
ELSE 0
END
)/SUM(cost)
FROM t;
Example
A CASE expression can be used in place of any value-expression.
SELECT *
FROM t
WHERE x = CASE y
WHEN 2
THEN 1001
WHEN 5
THEN 1002
END;
Example
The following example shows how to combine a CASE expression with a concatenation operator:
SELECT prodID, CASE prodSTATUS
WHEN 1
THEN 'SENT'
ELSE 'BACK ORDER'
END || ' STATUS'
FROM t1;
Example of Using UDT Data Types in Value Expressions
You use value_expression_1 through value_expression_n to test for equality in a valued CASE expression.
For these examples, the table is defined as follows:
create table udtval038_t1(id integer, udt1 testcircleudt, udt2 testrectangleudt) PRIMARY INDEX (id);
The following example shows a valued CASE expression, where all value expressions are of the same UDT data type:
SELECT CASE udt1
WHEN new testcircleudt('1,1,2,yellow,circ')
THEN 'Row 1'
WHEN new testcircleudt('2,2,4,purple,circ')
THEN 'Row 2'
WHEN new testcircleudt('3,3,9,green,circ')
THEN 'Row 3'
ELSE 'Row is NULL'
END
FROM t1;
*** Query completed. 4 rows found. One column returned.
<CASE expression>
------------------
Row 3
Row 1
Row is NULL
Row 2
However, the following example does not complete successfully because testrectangleudt does not match the other UDT data types:
SELECT CASE udt1
WHEN new testcircleudt('1,1,2,yellow,circ')
THEN 'Row 1'
WHEN new testrectangleudt('2,2,4,4,purple,rect')
THEN 'Row 2'
WHEN new testcircleudt('3,3,9,green,circ')
THEN 'Row 3'
ELSE 'Row is NULL'
END
FROM t1;
Example of Using UDT Data Types in Scalar Expressions
You use scalar_expression_n and scalar_expression_m as the expressions to return on when the equality comparison on a valued or searched CASE expression evaluates to TRUE, or the value to return on in an ELSE condition.
For these examples, the table is defined as follows:
create table udtval038_t1(id integer, udt1 testcircleudt, udt2 testrectangleudt) PRIMARY INDEX (id);
Following is an example of a searched CASE Expression where all scalar expressions are of the same UDT data type.
Note: The search_condition_n can be a different UDT data type than the scalar_expression_n.
SELECT * FROM udtval038_t1
WHERE udt1 = CASE
WHEN udt2 <> new testrectangleudt('2,2,4,4,pink,rect')
THEN new testcircleudt('1,1,2,blue,circ')
ELSE new testcircleudt('2,2,4,purple,circ')
END;
*** Query completed. 2 rows found. 3 columns returned.
id udt1
----------- -----------------------------------------------
1 1, 1, 2, yellow, circ
2 2, 2, 4, purple, circ
However, the following example does not complete successfully because the scalar expressions are of different data types.
SELECT * FROM udtval038_t1
WHERE udt1 = CASE
WHEN udt2 <> new testrectangleudt('2,2,4,4,pink,rect')
THEN new testcircleudt('1,1,2,blue,circ')
ELSE new testrectangleudt('2,2,4,4,purple,rect')
END;