15.00 - Valued CASE Expression - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)
Last Update
2018-09-24

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:

  • The data type of value_expression_1 through value_expression_n must have the same UDT data type if one of them has a UDT data type.
  • scalar_expression_n and scalar_expression_m must be the same UDT data type if one them has a UDT data type.
  • 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

    “Error Conditions” on page 569.

    the result data type of a CASE expression

    “Rules for the CASE Expression Result Type” on page 570.

    format of the result of a CASE expression

    “Format for a CASE Expression” on page 575.

    nulls and CASE expressions

    “CASE and Nulls” on page 576.

    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;