COALESCE In Teradata
Last updated on Dec Fri, 2022 5435
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.
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
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!
As a senior Technical Content Writer for HKR Trainings, Gayathri has a good comprehension of the present technical innovations, which incorporates perspectives like Business Intelligence and Analytics. She conveys advanced technical ideas precisely and vividly, as conceivable to the target group, guaranteeing that the content is available to clients. She writes qualitative content in the field of Data Warehousing & ETL, Big Data Analytics, and ERP Tools. Connect me on LinkedIn.
Upcoming Teradata Training Online classes
|Batch starts on 6th Oct 2023||
|Batch starts on 10th Oct 2023||
|Batch starts on 14th Oct 2023||