CASE

Description

The CASE statement in Redshift lets you return values based on conditions, enabling conditional logic in queries. It has two forms: simple and searched. (Redshift SQL Language Reference Conditionals: Case).

Simple Case

A simple CASE statement provides conditional execution based on equality of operands.

Simple Case are fully supported by Snowflake.

Grammar Syntax

CASE search-expression
WHEN expression [, expression [ ... ]] THEN
  statements
[ WHEN expression [, expression [ ... ]] THEN
  statements
  ... ]
[ ELSE
  statements ]
END CASE;

Sample Source Patterns

Input Code:

Redshift

IN -> Redshift_01.sql

CREATE OR REPLACE PROCEDURE proc1(x INT)
LANGUAGE plpgsql
AS $$
BEGIN
  CASE x
WHEN 1, 2 THEN
  NULL;
ELSE
  NULL;
END CASE;                  
END;
$$;

Output Code:

Redshift

OUT -> Redshift_01.sql

CREATE OR REPLACE PROCEDURE proc1 (x INT)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "02/14/2025",  "domain": "test" }}'
AS $$
BEGIN
  CASE x
    WHEN 1 THEN
      NULL;
    WHEN 2 THEN
      NULL;
   ELSE
     NULL;
  END CASE;
END;
$$;

Searched Case

Searched Case are fully supported by Snowflake.

Grammar Syntax

CASE
WHEN boolean-expression THEN
  statements
[ WHEN boolean-expression THEN
  statements
  ... ]
[ ELSE
  statements ]
END CASE;

Sample Source Patterns

Input Code:

Redshift

IN -> Redshift_02.sql

CREATE PROCEDURE PROC1 (paramNumber int)
LANGUAGE plpgsql
AS $$
DECLARE
    result VARCHAR(100);	
BEGIN
    IF paramNumber = 0 THEN
      result := 'zero';
    ELSIF paramNumber > 0 THEN
      result := 'positive';
    ELSIF paramNumber < 0 THEN
      result := 'negative';
    ELSE
      result := 'NULL';
    END IF;
END;
$$;

Output Code:

Redshift

OUT -> Redshift_02.sql

CREATE PROCEDURE PROC1 (paramNumber int)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "02/10/2025",  "domain": "test" }}'
AS $$
    DECLARE
      result VARCHAR(100);
BEGIN
      IF (paramNumber = 0) THEN
        result := 'zero';
      ELSEIF (paramNumber > 0) THEN
        result := 'positive';
      ELSEIF (paramNumber < 0) THEN
        result := 'negative';
      ELSE
        result := 'NULL';
      END IF;
END;
$$;

CASE Without ELSE

In Redshift, when a CASE expression is executed and none of the validated conditions are met, and there is no ELSE defined, the exception 'CASE NOT FOUND' is triggered. In Snowflake, the code executes but returns no result. To maintain the same functionality in Snowflake in this scenario, an exception with the same name will be declared and executed if none of the CASE conditions are met.

Case Without Else are fully supported by Snowflake.

Input Code:

Redshift

IN -> Redshift_03.sql

CREATE OR REPLACE PROCEDURE procedure1 (input_value INT)
AS $$
BEGIN
  CASE input_value
  WHEN 1 THEN
   NULL;
  END CASE;
END;
$$ LANGUAGE plpgsql;

Output Code:

Redshift

OUT -> Redshift_03.sql

CREATE OR REPLACE PROCEDURE procedure1 (input_value INT)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "03/03/2025",  "domain": "test" }}'
AS $$
    DECLARE
      case_not_found EXCEPTION (-20002, 'Case not found.');
BEGIN
  CASE input_value
  WHEN 1 THEN
   NULL;
  ELSE
   RAISE case_not_found;
  END CASE;
END;
$$;

Known Issues

There are no known issues.

Related EWIs.

There are no related EWIs.