NULLIF function

If its arguments are equal,NULLIF returns NULL , else it returns its first argument, scalar_expression_1.

For the following full CASE expression NULLIF is a shorthand expression :

CASE  WHEN scalar_expression_1=scalar_expression_2  THEN NULL  ELSE scalar_expression_1  END

The following examples show queries on the following table:

CREATE TABLE Membership

(FullName CHARACTER(39)

,Age SMALLINT

,Code CHARACTER(4) );

Example 1:

The following is the Teradata SQL NULLIFZERO(Age) function ANSI- compliant form of theand is more versatile.

SELECT FullName, NULLIF (Age,0) FROM Membership;

Example 2:

In the following query, blanks indicate no value.

SELECT FullName, NULLIF (Code, ' ') FROM Membership;

Example 3:

The following example uses NULLIF in an expression with an arithmetic operator.

SELECT NULLIF(Age,0) * 100;

Tweet