NVL

Conditional Function

Description

Returns the value of the first expression that isn't null in a series of expressions. When a non-null value is found, the remaining expressions in the list aren't evaluated. (Redshift SQL Language Reference NVL function)

In RedShift, NVL can contain 2 or more arguments, while in Snowflake NVL function only accepts 2 arguments. In order to emulate the same behavior, NVL with 3 or more arguments is transformed to COALESCE function.

This function is fully supported by Snowflake.

Grammar Syntax

NVL( expression, expression, ... )

Sample Source Patterns

Input Code:

Redshift

IN -> Redshift_01.sql

CREATE TABLE suppliers (
  supplier_id INT PRIMARY KEY,
  supplier_name VARCHAR(30),
  phone_region_1 VARCHAR(15),
  phone_region_2 VARCHAR(15),
  phone_region_3 VARCHAR(15));

INSERT INTO suppliers(supplier_id, supplier_name, phone_region_1, phone_region_2, phone_region_3)
  VALUES(1, 'Company_ABC', NULL, '555-01111', NULL),
        (2, 'Company_DEF', '555-01222', NULL, NULL),
        (3, 'Company_HIJ', '555-01333', '555-01444', NULL),
        (4, 'Company_KLM', NULL, NULL, NULL); 

SELECT NVL(phone_region_1, phone_region_2) IF_REGION_1_NULL,
       NVL(phone_region_2, phone_region_1) IF_REGION_2_NULL,
       NVL(phone_region_2, phone_region_1, phone_region_3) THREE_ARG_NVL
  FROM suppliers
  ORDER BY supplier_id;

Results

IF_REGION_1_NULL	IF_REGION_2_NULL	IF_REGION_3_NULL
555-01111	555-01111	555-01111
555-01222	555-01222	555-01222
555-01333	555-01444	555-01444
null	null	null

Output Code:

Snowflake

OUT -> Redshift_01.sql

CREATE TABLE suppliers (
  supplier_id INT PRIMARY KEY,
  supplier_name VARCHAR(30),
  phone_region_1 VARCHAR(15),
  phone_region_2 VARCHAR(15),
  phone_region_3 VARCHAR(15))
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "11/26/2024",  "domain": "test" }}';

INSERT INTO suppliers (supplier_id, supplier_name, phone_region_1, phone_region_2, phone_region_3)
  VALUES(1, 'Company_ABC', NULL, '555-01111', NULL),
        (2, 'Company_DEF', '555-01222', NULL, NULL),
        (3, 'Company_HIJ', '555-01333', '555-01444', NULL),
        (4, 'Company_KLM', NULL, NULL, NULL);

SELECT NVL(phone_region_1, phone_region_2) IF_REGION_1_NULL,
       NVL(phone_region_2, phone_region_1) IF_REGION_2_NULL,
       COALESCE(phone_region_2, phone_region_1, phone_region_3) THREE_ARG_NVL
  FROM
       suppliers
  ORDER BY supplier_id;

Results

IF_REGION_1_NULL	IF_REGION_2_NULL
555-01111	555-01111
555-01222	555-01222
555-01333	555-01444
null	null

Known Issues

There are no known issues.

Related EWIs

There are no known issues.