Teradata 简明教程

Teradata - CASE and COALESCE

本章介绍 Teradata 的 CASE 和 COALESCE 函数。

This chapter explains the CASE and COALESCE functions of Teradata.

CASE Expression

CASE 表达式针对条件或 WHEN 子句评估每一行，并返回第一次匹配的结果。如果没有匹配项，则返回 ELSE 部分的结果。

CASE expression evaluates each row against a condition or WHEN clause and returns the result of the first match. If there are no matches then the result from ELSE part of returned.

Syntax

以下是 CASE 表达式的语法。

Following is the syntax of the CASE expression.

CASE <expression>
WHEN <expression> THEN result-1
WHEN <expression> THEN result-2

ELSE
   Result-n
END

Example

考虑以下 Employee 表。

Consider the following Employee table.

EmployeeNo

FirstName

LastName

JoinedDate

DepartmentNo

BirthDate

101

Mike

James

3/27/2005

1/5/1980

102

Robert

Williams

4/25/2007

3/5/1983

103

Peter

Paul

3/21/2007

4/1/1983

104

Alex

Stuart

2/1/2008

11/6/1984

105

Robert

James

1/4/2008

12/1/1984

以下示例评估 DepartmentNo 列，如果部门编号为 1，则返回 1；如果部门编号为 3，则返回 2；否则，返回“无效部门”值。

The following example evaluates the DepartmentNo column and returns value of 1 if the department number is 1; returns 2 if the department number is 3; otherwise it returns value as invalid department.

SELECT
   EmployeeNo,
CASE DepartmentNo
   WHEN 1 THEN 'Admin'
   WHEN 2 THEN 'IT'
ELSE 'Invalid Dept'
   END AS Department
FROM Employee;

执行以上查询后，将产生以下输出。

When the above query is executed, it produces the following output.

*** Query completed. 5 rows found. 2 columns returned.
*** Total elapsed time was 1 second.
 EmployeeNo    Department
-----------   ------------
   101         Admin
   104         IT
   102         IT
   105         Invalid Dept
   103         IT

上面的 CASE 表达式也可以写成以下形式，产生的结果与上面相同。

The above CASE expression can also be written in the following form which will produce the same result as above.

SELECT
   EmployeeNo,
CASE
   WHEN DepartmentNo = 1 THEN 'Admin'
   WHEN  DepartmentNo = 2 THEN 'IT'
ELSE 'Invalid Dept'
   END AS Department
FROM Employee;

COALESCE

COALESCE 是一个语句，返回表达式中的第一个非空值。如果表达式的所有参数都计算为空值，则它返回 NULL。以下是语法。

COALESCE is a statement that returns the first non-null value of the expression. It returns NULL if all the arguments of the expression evaluates to NULL. Following is the syntax.

Syntax

COALESCE(expression 1, expression 2, ....)

Example

SELECT
   EmployeeNo,
   COALESCE(dept_no, 'Department not found')
FROM
   employee;

NULLIF

NULLIF 语句在参数相等时返回 NULL。

NULLIF statement returns NULL if the arguments are equal.

Syntax

以下是 NULLIF 语句的语法。

Following is the syntax of the NULLIF statement.

NULLIF(expression 1, expression 2)

Example

以下示例在 DepartmentNo 等于 3 时返回 NULL。否则，返回 DepartmentNo 值。

The following example returns NULL if the DepartmentNo is equal to 3. Otherwise, it returns the DepartmentNo value.

SELECT
   EmployeeNo,
   NULLIF(DepartmentNo,3) AS department
FROM Employee;

以上查询返回以下记录。您可以看到员工 105 的部门号为 NULL。

The above query returns the following records. You can see that employee 105 has department no. as NULL.

*** Query completed. 5 rows found. 2 columns returned.
*** Total elapsed time was 1 second.
 EmployeeNo      department
-----------  ------------------
    101              1
    104              2
    102              2
    105              ?
    103              2