NVL2

Takes three arguments. If the first argument is not NULL, it returns the second argument, otherwise it returns the third argument. The data types of the second and third arguments are implicitly cast to a common type if they don't agree, similar to COALESCE.

Behavior Type

Immutable

Syntax

NVL2 ( expression1 , expression2 , expression3 );

Parameters

  • If expression1 is not null, then NVL2 returns expression2.
  • If expression1 is null, then NVL2 returns expression3.

Notes

Arguments two and three can have any data type supported by Vertica.

Implementation is equivalent to the CASE expression:

CASE WHEN expression1 IS NOT NULL THEN expression2 ELSE expression3 END;

Examples

In this example, expression1 is not null, so NVL2 returns expression2:

SELECT NVL2('very', 'fast', 'database'); 
nvl2 
------
 fast
(1 row)

In this example, expression1 is null, so NVL2 returns expression3:

SELECT NVL2(null, 'fast', 'database');   
nvl2   
----------
 database
(1 row)

In the following example, expression1 (title) contains nulls, so NVL2 returns expression3 ('Withheld') and also substitutes the non-null values with the expression 'Known':

SELECT customer_name, NVL2(title, 'Known', 'Withheld') 
as title
FROM customer_dimension
ORDER BY title;
     customer_name      | title 
------------------------+-------
 Alexander I. Lang      | Known
 Steve S. Harris        | Known
 Daniel R. King         | Known
 Luigi I. Sanchez       | Known
 Duncan U. Carcetti     | Known
 Meghan K. Li           | Known
 Laura B. Perkins       | Known
 Samantha V. Robinson   | Known
 Joseph P. Wilson       | Known
 Kevin R. Miller        | Known
 Lauren D. Nguyen       | Known
 Emily E. Goldberg      | Known
 Darlene K. Harris      | Known
 Meghan J. Farmer       | Known
 Bettercare             | Withheld
 Ameristar              | Withheld
 Initech                | Withheld
(17 rows)

See Also