Teradata CASE & COALESCE

CASE and COALESCE both functions are used in Teradata for different purposes. Both functions have different functionalities.

CASE Expression

Teradata CASE statement provides the flexibility to fetch alternate values for a column base on the condition specified in the 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 the ELSE part of the return.

Syntax

Following is the syntax of the CASE expression.

Example

Consider the following Employee record in the below table.

In the above example, we evaluate the Marital_Status column. It returns 1 if the marital status is Single and returns 2 if the marital status is married. Otherwise, it returns the value as Not Sure.

Now, we will apply the CASE statement on Marital_Status column as follows:

After executing the above code, it produces the following output.

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

COALESCE Expression

Teradata COALESCE is used for NULL handling. The COALESCE is a statement that returns the first non-null value of the expression. It returns NULL if all the arguments of the expression evaluate to NULL. Following is the syntax.

Syntax

Here is the basic syntax of the COALESCE function:

Example

Consider the following Student table.

Now we can prioritize which phone number to select using COALESCE function as follows:

In the above example, we will search for Landline_no first. If that is NULL, it will search for Mobile_no, respectively. If both the numbers are NULL, it will return not available. And if none of the argument is returning, not NULL value, it will return the default value from those columns.

When we execute the above query, it generates the following output.

NULLIF

The NULLIF statement returns NULL if the arguments are equal.

Syntax

Following is the syntax of the NULLIF statement.

Example

The following example returns NULL if the Mobile_No is equal to 0. Otherwise, it returns the Mobile_No value.

The above query returns the following records. We can see that Roll_No 101 and 104 have Mobile as NULL.