As helpful as the ZEROIFNULL is, it only converts a NULL into a zero. The newer ANSI standard COALESCE can also convert a NULL to a zero. However, it can convert a NULL value to any data value as well. The COALESCE searches a value list, ranging from one to many values, and returns the first Non-NULL value it finds. At the same time, it returns a NULL if all values in the list are NULL.
To use the COALESCE, the SQL must pass the name of a column to the function. The data in the column is then compared for a NULL. Although one column name is all that is required, normally more than one column is normally passed to it. Additionally, a literal value, which is never NULL, can be returned to provide a default value if all of the previous column values are NULL.
Whereas NULLIF works with two parameters and compares a column with a value passed to it, the COALESCE can examine many values and continues to check each data value until it finds one that is not a NULL and that value is returned.
However, if all the values passed to the function are NULL, it has no choice and can only return a NULL. The values passed to the COALESCE function can be via column names or a literal value.
The syntax for the COALESCE follows:
In the above syntax the <column-list> is a list of columns. It is written as a series of column names separated by commas.
In the above SQL and its output:
Like the ZEROIFNULL, one of the best uses for the COALESCE is in a mathematics formula. In an earlier chapter it was seen that anytime a NULL is used in math, the answer is a NULL. Therefore, the COALESCE can convert a NULL to a zero so that an answer is returned.
The next SELECT displays the student's last name and GPA. However, if there is no GPA, it includes the phrase "Missing GPA" in the output using the COALESCE to search Grade points for a NULL:10 Rows Returned
The next example uses basically the same SELECT as above, but adds a second column to the column list of the COALESCE.
In this example, 2 columns and a literal are used in the COALESCE. Since Johnson had a first name, the literal is not displayed. It is there as an insurance policy in case both columns contained NULL. COALESCE is a great tool any time there is a need to display a single column and insure that missing data is replaced with a substitute value.
Teradata Related Interview Questions
|Microstrategy Interview Questions||Informatica Interview Questions|
|MySQL Interview Questions||Oracle 11g Interview Questions|
|Hadoop Interview Questions||TeraData DBA Interview Questions|
|MYSQL DBA Interview Questions||Database Administration Interview Questions|
|DB2 SQL Programming Interview Questions||Hadoop Administration Interview Questions|
|Java Hadoop Developer Interview Questions||Informatica MDM Interview Questions|
|Informatica Admin Interview Questions||Hadoop Testing Interview Questions|
Teradata Related Practice Tests
|Microstrategy Practice Tests||Informatica Practice Tests|
|MySQL Practice Tests||Oracle 11g Practice Tests|
|Hadoop Practice Tests||TeraData DBA Practice Tests|
|MYSQL DBA Practice Tests||Database Administration Practice Tests|
|DB2 SQL Programming Practice Tests||Hadoop Administration Practice Tests|
Teradata Parallel Architecture
Fundamental Sql Using Select
On-line Help And Show Commands
Date And Time Processing
Character String Processing
Reporting Totals And Subtotals
Data Definition Language
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.