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])
Teradata Training
- Master Your Craft
- Lifetime LMS & Faculty Access
- 24/7 online expert support
- Real-world & Project Based Learning
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
Subscribe to our YouTube channel to get new updates..!
Now, we can define the priority of choosing the phone number
SELECTStudent 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.
Enroll in our Datastage Administrator Training program today and elevate your skills!
Teradata Training
Weekday / Weekend Batches
Related Articles:
