COALESCE

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.

For more information, please refer to COALESCE function.

Grammar Syntax

COALESCE( expression, expression, ... )

This function is fully supported by Snowflake.

Sample Source Patterns

Input Code:

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));

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

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

Output Code:

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))
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/27/2024" }}';

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

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

There are no known issues.

PreviousConditional Functions NextNVL

Last updated 4 months ago

Description

Grammar Syntax

Sample Source Patterns

Input Code:

Related EWIs