Vertica NVL and NVL2 Functions with Examples

  • Post author:
  • Post last modified:April 2, 2019
  • Post category:Vertica
  • Reading time:6 mins read

Data warehouse basically integrates data from various heterogeneous sources. You may not receive perfect data every time you process or refresh data warehouse tables. It is very common to receive NULL values as most of the time source system will generate NULLs for any unknown or blank values. NULL values should be handled properly when you generate reports out of tables. In this article, we will check how to handle NULL values using Vertica NVL and NVL2 functions with some examples to demonstrate the same.

Vertica NVL Function

The NVL function returns first non-null value from the list of expressions. The NVL function accepts two parameters; column or expression and replacement values. The NVL and NVL2 functions usually replaces a NULL value in the given expression or column with a replacement string. This function returns the first argument if it is not null, otherwise the second argument.

The Vertica NVL function is equivalent to the SQL COALESCE function. If all expressions or provided parameters are null, the result is null. It returns first non-null expression in the list, when a non-null value is found, the remaining expressions in the list are not evaluated.

Vertica NVL Syntax

The Vertica SQL NVL is similar to other relational database NVL function. Below is the syntax:

NVL (exp1, exp2);

Returns ext1 is not null, otherwise exp2 is returned.

Vertica NVL Function Examples

NVL function is used to replace NULL values in the expression or columns. 

SELECT qty, 
       NVL(qty, 0) AS qty_new, 
       flag 
FROM   (SELECT NULL AS qty, 
               1    AS flag) a; 
SELECT name, 
       NVL(qty, ‘NA’) AS name_new, 
       flag 
FROM   (SELECT NULL AS name, 
               1    AS flag) a;

Vertica NVL2 Function

The NVL2 function is an extension of NVL function. You can compare NVL2 function with CASE statement. NVL2 function will return the second argument if the first argument is non-null, otherwise third argument will be returned.

Vertica NVL2 Syntax

The NVL2 function accepts three arguments, an expression or column name, first replacement values and second replacement values.

Below is the NVL2 syntax:

NVL2 ( expr1 , expr2 , expr2 );

 You can compare NVL2 as an IF statement in any programming language. It returns expr2 if expr1 is non-null, otherwise expr2 is returned.

Related Articles:

Vertica NVL2 Function Examples

Just like NVL functions, NVL2 is also used to replace null values. Below is the example of NVL2 function:

SELECT qty, 
       NVL2(qty, 1,0) AS qty_new, 
       flag 
FROM   (SELECT NULL AS qty, 
               1    AS flag) a;

The NVL and NVL2 functions are heavily used in join conditions to replace null values with some custom values so that a condition should not return false output.

Related Article:

Vertica NVL2 in place of CASE

NVL2 implementation is SQL is equivalent to CASE statement. You can implement CASE instead of NVL2. Below is the syntax and example:

CASE 
  WHEN expr1 IS NOT NULL THEN expr2 
  ELSE expr3 
END;

For example;

SELECT qty, 
       CASE 
         WHEN qty IS NOT NULL THEN 1 
         ELSE 0 
       END             AS case_1, 
       Nvl2(qty, 1, 0) AS qty_new, 
       flag 
FROM   (SELECT NULL AS qty, 
               1    AS flag) a;

Use CASE statement if there are multiple conditions to be verified.

Hope this helps 🙂

Tags: ,