CASE

Translation reference for CASE statements

Description

The CASE statement chooses from a sequence of conditions and runs a corresponding statement. For more information regarding Oracle CASE, check here.

Simple case

Oracle CASE Syntax

[ <<label>> ] CASE case_operand
  WHEN boolean_expression THEN statement ;
  [ WHEN boolean_expression THEN statement ; ]...
  [ ELSE statement [ statement ]... ;
END CASE [ label ] ;

Snowflake Scripting CASE Syntax

CASE ( <expression_to_match> )
    WHEN <expression> THEN
        <statement>;
        [ <statement>; ... ]
    [ WHEN ... ]
    [ ELSE
        <statement>;
        [ <statement>; ... ]
    ]
END [ CASE ] ;

Searched case

Oracle CASE Syntax

[ <<label>> ] CASE
  WHEN boolean_expression THEN statement ;
  [ WHEN boolean_expression THEN statement ; ]...
  [ ELSE statement [ statement ]... ;
END CASE [ label ];

Snowflake Scripting CASE Syntax

CASE
    WHEN <boolean_expression> THEN
        <statement>;
        [ <statement>; ... ]
    [ WHEN ... ]
    [ ELSE
        <statement>;
        [ <statement>; ... ]
    ]
END [ CASE ] ;

Sample Source Patterns

Sample auxiliar table

Oracle

CREATE TABLE case_table(col varchar(30));

Snowflake

CREATE OR REPLACE TABLE PUBLIC.case_table (col varchar(30));

Simple Case

Oracle

Query

CREATE OR REPLACE PROCEDURE caseExample1 ( grade NUMBER )
IS
RESULT VARCHAR(20);
BEGIN
   <<CASE1>>
   CASE grade
    WHEN 10 THEN RESULT:='Excellent';
    WHEN 9 THEN RESULT:='Very Good';
    WHEN 8 THEN RESULT:='Good';
    WHEN 7 THEN RESULT:='Fair';
    WHEN 6 THEN RESULT:='Poor';
    ELSE RESULT:='No such grade';
  END CASE CASE1;
  INSERT INTO CASE_TABLE(COL) VALUES (RESULT);
END;

CALL caseExample1(6);
CALL caseExample1(4);
CALL caseExample1(10);
SELECT * FROM CASE_TABLE;

Result

|COL          |
|-------------|
|Poor         |
|No such grade|
|Excellent    |

Snowflake Scripting

Query

CREATE OR REPLACE PROCEDURE PUBLIC.caseExample1 (grade FLOAT )
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   DECLARE
      RESULT STRING;
   BEGIN
      /*** MSC-WARNING - MSCEWI3107 - LABEL DECLARATION FOR A STATEMENT IS NOT SUPPORTED BY SNOWFLAKE SCRIPTING ***/
      /*<<CASE1>>*/
      CASE :grade
         WHEN 10 THEN
            RESULT := 'Excellent';
         WHEN 9 THEN
            RESULT := 'Very Good';
         WHEN 8 THEN
            RESULT := 'Good';
         WHEN 7 THEN
            RESULT := 'Fair';
         WHEN 6 THEN
            RESULT := 'Poor';
      ELSE
         RESULT := 'No such grade';
      END CASE;
      INSERT INTO PUBLIC.CASE_TABLE(COL) VALUES (:RESULT);
   END;
$$;

CALL PUBLIC.caseExample1(6);

CALL PUBLIC.caseExample1(4);

CALL PUBLIC.caseExample1(10);

SELECT * FROM PUBLIC.CASE_TABLE;

Result

|COL          |
|-------------|
|Poor         |
|No such grade|
|Excellent    |

Searched Case

Oracle

Query

CREATE OR REPLACE PROCEDURE caseExample2 ( grade NUMBER )
IS
RESULT VARCHAR(20);
BEGIN
    <<CASE1>>
    CASE
    	WHEN grade = 10 THEN RESULT:='Excellent';
    	WHEN grade = 9 THEN RESULT:='Very Good';
    	WHEN grade = 8 THEN RESULT:='Good';
    	WHEN grade = 7 THEN RESULT:='Fair';
    	WHEN grade = 6 THEN RESULT:='Poor';
    	ELSE RESULT:='No such grade';
  END CASE CASE1;
  INSERT INTO CASE_TABLE(COL) VALUES (RESULT);
END;

CALL caseExample2(6);
CALL caseExample2(4);
CALL caseExample2(10);
SELECT * FROM CASE_TABLE;

Result

|COL          |
|-------------|
|Poor         |
|No such grade|
|Excellent    |

Snowflake Scripting

Query

CREATE OR REPLACE PROCEDURE PUBLIC.caseExample2 (grade FLOAT )
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   DECLARE
      RESULT STRING;
   BEGIN
      /*** MSC-WARNING - MSCEWI3107 - LABEL DECLARATION FOR A STATEMENT IS NOT SUPPORTED BY SNOWFLAKE SCRIPTING ***/
      /*<<CASE1>>*/
      CASE
         WHEN grade = 10 THEN
            RESULT := 'Excellent';
         WHEN grade = 9 THEN
            RESULT := 'Very Good';
         WHEN grade = 8 THEN
            RESULT := 'Good';
         WHEN grade = 7 THEN
            RESULT := 'Fair';
         WHEN grade = 6 THEN
            RESULT := 'Poor';
      ELSE
         RESULT := 'No such grade';
      END CASE;
      INSERT INTO PUBLIC.CASE_TABLE(COL) VALUES (:RESULT);
   END;
$$;

CALL PUBLIC.caseExample2(6);

CALL PUBLIC.caseExample2(4);

CALL PUBLIC.caseExample2(10);

SELECT * FROM PUBLIC.CASE_TABLE;

Result

|COL          |
|-------------|
|Poor         |
|No such grade|
|Excellent    |

Known issues

1. Labels are not supported in Snowflake Scripting CASE syntax

The labels are commented out or removed depending on their position.

Related EWIS

No related EWIs.