NULLIF Expression

Returns a null value if the two specified expressions are equal; otherwise, returns the first expression.

Restriction:

The BASE file format processes a null value as DOUBLE values in some situations and as a blank string in other situations. For more information, see How FedSQL Processes Nulls and SAS Missing Values.

Syntax

NULLIF(expression-1, expression-2)

Arguments

expression

specifies any valid SQL expression.

Data type	All data types are valid.
See	<sql-expression>
See	FedSQL Expressions

Details

The NULLIF expression compares two SQL expressions and, if they are equal, returns a null value. The NULLIF expression enables you to replace a missing or inapplicable value with a null value and to use SQL's behavior for null values.

Comparisons

The NULLIF expression is a shorthand syntax for a special CASE expression. For example, if a student misses a test, a -1 is entered in the GRADES table. To replace this -1 with a null value, you could use the following CASE code.

update grades
   set testscore =
    CASE
      when testscore = '-1' then null
      ELSE testscore
     END;

The following code uses the shorter NULLIF expression.

update grades
   set testscore = NULLIF(testscore, '-1');

The IFNULL function compares two SQL expressions and returns the second SQL expression if the first SQL expression is a null value. The NULLIF expression compares two SQL expressions and returns a null value if the two SQL expressions are equal.

Example

Table: WORLDCITYCOORDS

missingLong= '.L';
update worldcitycoords
set longitude = nullif(missingLong, '.');
select city
   from worldcitycoords
      where Longitude='.L';

NULLIF Expression

Syntax

Arguments

expression

Details

Comparisons

Example

See Also