Teradata - CASE and COALESCE
This chapter explains the CASE and COALESCE functions of Teradata.
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.
Following is the syntax of the CASE expression.
CASE <expression> WHEN <expression> THEN result-1 WHEN <expression> THEN result-2 ELSE Result-n END
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 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 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.
COALESCE(expression 1, expression 2, ....)
SELECT EmployeeNo, COALESCE(dept_no, 'Department not found') FROM employee;
NULLIF statement returns NULL if the arguments are equal.
Following is the syntax of the NULLIF statement.
NULLIF(expression 1, expression 2)
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