As handy as NULLIFZERO is, it only converts a zero to a NULL. Like its predecessor, the newer ANSI standard NULLIF function also can convert a zero to a NULL. However, it can convert anything to a NULL. To use the NULLIF, the SQL must pass the name of the column to compare and the value to compare for equal.
The following is the syntax for using the NULLIF function.
To show the operation of the NULLIF, literal values are shown in the next example:
In the above SQL and its output:
Like the NULLIFZERO the NULLIF is great for situations when the SQL is doing division and aggregation. If a need arises to eliminate a zero or any specific value from the aggregation, the NULLIF can convert that value to a NULL. Earlier we discussed aggregates and the fact that they do ignore a NULL value.
An example of using the NULLIF in division and aggregation follows:
Without the NULLIF, we get an error. Why?
The reason is that the grade point value is multiplied by 2 with the result being zero. The problem occurs when the grade point is divided by 0 and the SQL aborts with a 2619 error condition.
In the next example, the NULLIF is added to the denominator of the first column to fix the division error:
There are two items to notice from this answer set. First, the 0 in Grade_pt is converted to a NULL and the 2619 error disappears. Next, the NULL value is multiplied by 2 with a result of NULL. Then, the value stored in Grade_pt is divided by a NULL and of course, the result is a NULL. Anytime a value is divided by itself times 2, .5 is the result. Therefore, all valid data values are combined in the output. The only other row(s) are those with a zero in the grade point column.
The second thing about the output is the heading. Notice that both headings contain the word CASE. Later in this chapter CASE is shown as a technique to test values. Now it is seen that the NULLIF and COALESCE both use CASE for their tests. Now that this is known, it also means that using alias or TITLE is probably a good idea to dress up the output.
The good news is that the NULLIF allows the SQL to complete and show values for all rows that do not contain a zero in the column used for division. These zero values probably need to be fixed in the long term.
For the second column in this SELECT, whenever Grade_pt contains a zero, it is converted to a NULL. The resulting NULL is passed to the AVG function and promptly ignored. Therefore, the resulting average will be a higher number than if the zero is allowed to become part of the overall average.
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.