COALESCE In Teradata

What is Coalesce in Teradata?

Coalesce is a function in Teradata that handles Null values of functions. Using this function the function returns the first NOT NULL value appeared in the given list of values. In other cases, if all the arguments in the list of expressions are evaluated as Null then the function returns Null.

Coalesce is used to check the appearance of Null in a given argument. If the function finds and NULL in the arguments it returns the default value is NULL.

Similarly, it checks for the NOT NULL values in the argument and returns the first NOT NULL value if it exists in the expression list.

Become a Teradata Certified professional by learning Teradata Training from hkrtrainings! 

Functional Syntax of Teradata Coalesce

COALESCE (Expression 1, Expression 2, Expression N, [Expression List])

Argument List can be Expression OR Columns Of A Table

There are conditions for returning NULL that have to be satisfied in the function to return NULL.

Condition 1: If in the given expression all the given arguments evaluate to NULL then only the function returns NULL.

Condition 2: If in the given arguments any expression holds values than the function returns the first occurred value in the expression.

Condition 3: Each COALESCE function must hold two arguments in the argument list.

Condition 4: It is also possible to have multiple columns under the COALESCE function

COALESCE (column 1, column 2, column 3, 0)

This function defines if in the given arguments

·         Column 1 is NULL then it will check column 2

·         If column 2 is NULL then it will check column 3

·         If column 3 is also NULL then it will keep moving till 0 and return 0 as output.

Example 1

If the query is written as

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

If you want to Explore more about Teradata? then read our updated article - Teradata Tutorial

Now, we can define the priority of choosing the phone number

SELECT

Student ID,

First_Name,

Last_Name,

COALESCE (Mobile_Number, Home_Number, Additional_Number, ‘Phone Number Not Available ’) As Phone_No FROM teradatapoint. student ;

This query will search for Mobile_Number, if it is NULL then it will search for Home_Number and if it is also then the control will move to Additional_Number. If any arguments will not return NOT NULL value, the function will return the default value.

This statement will bring the output as

*** Query completed. 3 rows found. 4 columns returned.
  *** Total elapsed time was 1 second.

 

     emp_no  first_name  last_name   Phone_No
 -----------  ----------  ----------  --------------------
         345  Amal        Roy         Phone# not available
         123  Alex        Martin                7209756747
         134  Sager       Sharma                  34567548

The example clearly shows Danny has not given any mobile number, wardra has printed the mobile number and sandy has given home number. So the output brings the exact results as per the query written here. When the function did not find any value it returned the Phone Number Not Available.

Teradata provides various functions and methods that can be used in relational database management. Due to advanced function, the developers can run queries and get results as per requirements.

Explore Teradata DBA Sample Resumes Download & Edit, Get Noticed by Top Employers! 

 Related Articles:

• CSUM in Teradata
• OLAP functions in Teradata
• Teradata SQL Assistant