Teradata - CASE and COALESCE

Advertisements

Previous Page
Next Page  

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.

EmployeeNo FirstName LastName JoinedDate DepartmentNo BirthDate
101 Mike James 3/27/2005 1 1/5/1980
102 Robert Williams 4/25/2007 2 3/5/1983
103 Peter Paul 3/21/2007 2 4/1/1983
104 Alex Stuart 2/1/2008 2 11/6/1984
105 Robert James 1/4/2008 3 12/1/1984

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

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 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
Advertisements