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

CASE

Purpose

Specifies alternate values for a conditional expression or expressions based on equality comparisons and conditions that evaluate to TRUE.

ANSI Compliance

This is ANSI SQL:2011 compliant.

Overview

CASE provides an efficient and powerful method for application developers to change the representation of data, permitting conversion without requiring host program intervention.

For example, you could code employee status as 1 or 2, meaning full-time or part-time, respectively. For efficiency, the system stores the numeric code but prints or displays the appropriate textual description in reports. This storage and conversion is managed by Teradata Database.

In addition, CASE permits applications to generate nulls based on information derived from the database, again without host program intervention. Conversely, CASE can be used to convert a null into a value.

Two Forms of CASE Expressions

CASE expressions are specified in these forms:

  • Valued CASE is described under “Valued CASE Expression” on page 560.
  • Searched CASE is described under “Searched CASE Expression” on page 565.
  • CASE Shorthands for Handling Nulls

    Two shorthand forms of CASE are provided to handle nulls:

  • COALESCE is described under “COALESCE Expression” on page 579.
  • NULLIF is described under “NULLIF Expression” on page 581.