Compatibility: Teradata Extension
The Teradata database software, for many years, has provided the user the ability to test for zero using the original NULLIFZERO function. The purpose of this function was to compare the data value in a column for a zero and when found, convert the zero, for the life of the SQL statement, to a NULL value.
The following syntax shows two different uses of the NULLIFZERO function, first on a column and then within an aggregate:
The next SELECT uses literal values to demonstrate the functionality of the NULLIFZERO:1 Row Returned
In the above SQL and its output:
The next SELECT shows what happens when a zero ends up in the denominator of a division request and then, how to avoid it using the NULLIFZERO in division:
To fix the 2619 error, this next technique might be used:10 Rows Returned
This can be a lifesaver when a zero will cause an incorrect result or cause the SQL statement to terminate in an error such as dividing by zero. Therefore, it is common to use NULLIFZERO for the divisor when doing a division operation.
Although dividing by NULL returns a NULL, the SQL continues to execute and returns all the values for rows containing good data. At the same time, it also helps to identify the row or rows that need to be fixed.
The next two examples show a different answer when the NULLIFZERO function is used inside an aggregate function to eliminate the ZERO:
First without NULLIFZERO:
Then with NULLIFZERO:5 Rows Returned
As seen in the above answer sets, the zero GPA value came out as is in the first one. Then, the NULLIFZERO converts it to a NULL in the second with FR being higher.
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.