15.00 - Searched 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

Searched CASE Expression

Purpose

Evaluates a search condition and returns one of a WHEN clause‑defined set of scalar values when it finds a value that evaluates to TRUE. If no TRUE test is found, then CASE returns the scalar value defined by an ELSE clause, or if omitted, NULL.

Syntax

where:

 

Syntax element …

Specifies …

search_condition_n

a predicate condition to be tested for truth.

scalar_expression_n

a scalar expression whose value is returned when search_condition_n is the first search condition that evaluates to TRUE.

scalar_expression_m

a scalar expression whose value is returned when no search_condition_n evaluates to TRUE.

ANSI Compliance

This is ANSI SQL:2011 compliant.

Usage Notes

WHEN clauses are processed sequentially.

The first WHEN clause search_condition_n that is TRUE returns the value of its associated scalar_expression_n as its result. The evaluation process then ends.

If no search_condition_n is TRUE, then scalar_expression_m, the argument of the ELSE clause, is the result.

If no ELSE clause is defined, then the default value for the result is NULL.

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>

Rules for WHEN Search Conditions

WHEN search conditions have the following properties:

  • Can take the form of any comparison operator, such as LIKE, =, or <>.
  • Can be a quantified predicate, such as ALL or ANY.
  • Can contain a scalar subquery.
  • Can contain joins of two tables.
  • For example:

       SELECT CASE 
       WHEN t1.x=t2.x THEN t1.y 
       ELSE t2.y 
       END FROM t1,t2;
       
  • Cannot contain SELECT statements.
  • 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 statement is equivalent to the first example of the valued form of CASE on “Example 1” on page 562:

       SELECT SUM(CASE 
                   WHEN part='1'
                   THEN cost
                   ELSE 0
                  END
                 ) / SUM(cost)
       FROM t;

    Example  

    CASE expressions can be used in place of any value-expressions.

    Note that the following example does not specify an ELSE clause. ELSE clauses are always optional in a CASE expression. If an ELSE clause is not specified and none of the WHEN conditions are TRUE, then a null is returned.

       SELECT * 
       FROM t 
       WHERE x = CASE 
                  WHEN y=2 
                  THEN 1
                  WHEN (z=3 AND y=5) 
                  THEN 2 
                 END;

    Example  

    The following example uses an ELSE clause.

       SELECT * 
       FROM t 
       WHERE x = CASE 
                  WHEN y=2 
                  THEN 1 
                  ELSE 2 
                 END;

    Example  

    The following example shows how using a CASE expression can result in significantly enhanced performance by eliminating multiple passes over the data. Without using CASE, you would have to perform multiple queries for each region and then consolidate the answers to the individual queries in a final report.

       SELECT SalesMonth, SUM(CASE 
                               WHEN Region='NE' 
                               THEN Revenue 
                               ELSE 0 
                              END),
                          SUM(CASE 
                               WHEN Region='NW' 
                               THEN Revenue 
                               ELSE 0 
                              END),
                          SUM(CASE 
                               WHEN Region LIKE 'N%' 
                               THEN Revenue 
                               ELSE 0 
                              END)
       AS NorthernExposure, NorthernExposure/SUM(Revenue),
       SUM(Revenue)
       FROM Sales
       GROUP BY SalesMonth;

    Example  

    All employees whose salary is less than $40000 are eligible for an across the board pay increase.

     

    IF your salary is less than …

    AND you have greater than this many years of service …

    THEN you receive this percentage salary increase …

    $30000.00

    8

    15

    $35000.00

    10

    10

    $40000.00

    5

    The following SELECT statement uses a CASE expression to produce a report showing all employees making under $40000, displaying the first 15 characters of the last name, the salary amount (formatted with $and punctuation), the number of years of service based on the current date (in the column named On_The_Job) and which of the four categories they qualify for: '15% Increase', '10% Increase', '05% Increase' or 'Not Qualified'.

       SELECT CAST(last_name AS CHARACTER(15))
          ,salary_amount (FORMAT '$,$$9,999.99')
          ,(date - hire_date)/365.25 (FORMAT 'Z9.99') AS On_The_Job
          ,CASE 
              WHEN salary_amount < 30000 AND On_The_Job > 8 
              THEN '15% Increase'
              WHEN salary_amount < 35000 AND On_The_Job > 10 
              THEN '10% Increase'
              WHEN salary_amount < 40000 AND On_The_Job > 10
              THEN '05% Increase'
              ELSE 'Not Qualified'
             END  AS Plan
       WHERE salary_amount < 40000
       FROM employee
       ORDER BY 4;

    The result of this query appears in the following table:

     

    last_name

    salary_amount

    On_The_Job

    Plan

    Trader

    $37,850.00

    20.61

    05% Increase

    Charles

    $39,500.00

    18.44

    05% Increase

    Johnson

    $36,300.00

    20.41

    05% Increase

    Hopkins

    $37,900.00

    19.99

    05% Increase

    Morrissey

    $38,750.00

    18.44

    05% Increase

    Ryan

    $31,200.00

    20.41

    10% Increase

    Machado

    $32,300.00

    18.03

    10% Increase

    Short

    $34,700.00

    17.86

    10% Increase

    Lombardo

    $31,000.00

    20.11

    10% Increase

    Phillips

    $24,500.00

    19.95

    15% Increase

    Rabbit

    $26,500.00

    18.03

    15% Increase

    Kanieski

    $29,250.00

    20.11

    15% Increase

    Hoover

    $25,525.00

    20.73

    15% Increase

    Crane

    $24,500.00

    19.15

    15% Increase

    Stein

    $29,450.00

    20.41

    15% Increase