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.
Related EWIS
No related EWIs.
Last updated
Was this helpful?