|
Teradata is one of the most prominent Database solutions that offer Relational Database Management features to the users. In today’s booming industry demand handling large databases and database warehouses is needed for an hour to ensure continuous business outcomes. Teradata works on a parallelism mechanism to provide high-end support to huge databases. Teradata offers N number of functions and methods to achieve the desired database management. With a robust security system and ease of accessing the data.
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!
COALESCE (Expression 1, Expression 2, Expression N, [Expression List])
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
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:
Batch starts on 5th Apr 2023, Weekday batch
Batch starts on 9th Apr 2023, Weekend batch
Batch starts on 13th Apr 2023, Weekday batch
.
©2023 HKR Trainings. All rights Reserved.