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

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

Searched case

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

Sample Source Patterns

Sample auxiliar table

CREATE TABLE case_table(col varchar(30));

Simple Case

Oracle

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;

Snowflake Scripting

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;

Searched Case

Oracle

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;

Snowflake Scripting

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;

Known issues

1. Labels are not supported in Snowflake Scripting CASE syntax

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

No related EWIs.

Last updated

Was this helpful?