NULLIF

NULLIF compares two expressions. If the values are equal, NULLIF returns a NULL; otherwise, the function returns the first expression.

SQL syntax

NULLIF(Expression1, Expression2)

Parameters

NULLIF has the following parameters:

Parameter Description

Expression1

The expression that is tested to see whether it is equal to Expression2. You cannot specify the literal NULL for Expression1.

Expression2

The expression that is tested to see whether it is equal to Expression1.

Description

  • If both parameters are numeric data types, Timesten determines the argument with the higher numeric precedence, implicitly converts the other argument to this data type, and returns this data type. If the parameters are not numeric data types, they must be in the same data type family.

  • LOB data types are not supported in NULLIF. The TIME data type is only supported if both columns are of the TIME data type.

  • The NULLIF function is logically equivalent to the following CASE expression: 

    CASE WHEN Expression1 = Expression2 THEN NULL ELSE Expression1 END

Note:

See "CASE Expressions" for more details.

Examples

The following example selects employees who have changed jobs since they were hired, which is indicated by a different job_id in the job_history table from the current job_id in the employees table. Thus, when you apply NULLIF to the old and new job_id entries, those that are the same returns a NULL; those that are different indicate those employees who have changed jobs. 

Command> SELECT e.last_name, NULLIF(e.job_id, j.job_id) "Old Job ID" 
         FROM employees e, job_history j 
         WHERE e.employee_id = j.employee_id 
         ORDER BY last_name, "Old Job ID";

< De Haan, AD_VP >
< Hartstein, MK_MAN >
< Kaufling, ST_MAN >
< Kochhar, AD_VP >
< Kochhar, AD_VP >
< Raphaely, PU_MAN >
< Taylor, SA_REP >
< Taylor, <NULL> >
< Whalen, AD_ASST >
< Whalen, <NULL> >
10 rows found.