Teradata - CASE and COALESCE

This chapter explains the CASE and COALESCE functions of Teradata.

CASE Expression

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

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

Consider the following Employee table.

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 invapd department.

SELECT 
   EmployeeNo, 
CASE DepartmentNo 
   WHEN 1 THEN  Admin  
   WHEN 2 THEN  IT  
ELSE  Invapd 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         Invapd Dept 
   103         IT

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  Invapd Dept  
   END AS Department  
FROM Employee;

COALESCE

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 statement returns NULL if the arguments are equal.

Syntax

Following is the syntax of the NULLIF statement.

NULLIF(expression 1, expression 2) 

Example

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;

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