Netezza NVL and NVL2 Functions with Examples

Netezza supports NVL and NVL2 functions.

Netezza NVL Function

The NVL function replaces a NULL value with a replacement string that you provide in the function. Returns the first argument if it is not null, otherwise it returns the second argument

The Netezza NVL function is equivalent to the Netezza SQL coalesce function. The Function will return the non-NULL value.

Note that, You cannot replace the blank value using NVL function. Some databases supports this nut Netezza does not.

Syntax:

NVL (exp,replacement-exp);

Returns ‘exp’ if not null otherwise ‘replacement-exp’ is returned.

Netezza NVL Function Example

Below is the example on usage of Netezza NVL function:

SYSTEM.ADMIN(ADMIN)=> select price, NVL(price, 0.0) as price1 from (select NULL as price) a; 
 PRICE | PRICE1 
-------+---------- 
 | 0.000000 
(1 row)

Netezza NVL2 Function

Netezza NVL2 Function returns the second argument if the first argument is not null, otherwise it returns the third argument. Netezza NVL2 is short hand for the Netezza case expression.

Read:

• Netezza Case Statement and its Usage
• Netezza nzsql Command and its Usage
• IBM Netezza DECODE Function Syntax and Examples

Syntax:

NVL2 (exp,replacement-exp1,replacement-exp2);

Returns ‘replacement-exp1’ if ‘exp’ is not null, otherwise ‘replacement-exp2’ is returned.

Netezza NVL2 Function Examples

SYSTEM.ADMIN(ADMIN)=> select price,curr, NVL2(price, 0.0,1) as price1, NVL2(curr,1,0) as curr1 from (select NULL as price,1 as curr) a; 
 PRICE | CURR | PRICE1 | CURR1 
-------+------+--------+------- 
 | 1 | 1 | 1 
(1 row)