CASE

Translation reference to convert Teradata CASE statement to Snowflake Scripting

Description

Provides conditional execution of statements based on the evaluation of the specified conditional expression or equality of two operands.

The CASE statement is different from the SQL CASE expression, which returns the result of an expression.

For more information regarding Teradata CASE, check here.

-- Simple CASE
CASE operant_1
[ WHEN operant_2 THEN
     statement
     [ statement ]... ]...
[ ELSE   
     statement
     [ statement ]... ]
END CASE;

-- Searched CASE
CASE
[ WHEN conditional_expression THEN
     statement
     [ statement ]... ]...
[ ELSE   
     statement
     [ statement ]... ]
END CASE;

Sample Source Patterns

Sample auxiliar table

Teradata

CREATE TABLE case_table(col varchar(30));

Snowflake

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

Simple Case

Teradata

Query

CREATE  PROCEDURE caseExample1 ( grade NUMBER )
BEGIN
    CASE grade
        WHEN 10 THEN INSERT INTO CASE_TABLE(COL) VALUES ('Excellent');
        WHEN 9 THEN INSERT INTO CASE_TABLE(COL) VALUES ('Very Good');
        WHEN 8 THEN INSERT INTO CASE_TABLE(COL) VALUES ('Good');
        WHEN 7 THEN INSERT INTO CASE_TABLE(COL) VALUES ('Fair');
        WHEN 6 THEN INSERT INTO CASE_TABLE(COL) VALUES ('Poor');
        ELSE INSERT INTO CASE_TABLE(COL) VALUES ('No such grade');
    END CASE;
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
$$
   BEGIN
      CASE ( grade)
         WHEN 10 THEN
            INSERT INTO PUBLIC.CASE_TABLE (COL) VALUES ('Excellent');
         WHEN 9 THEN
            INSERT INTO PUBLIC.CASE_TABLE (COL) VALUES ('Very Good');
         WHEN 8 THEN
            INSERT INTO PUBLIC.CASE_TABLE (COL) VALUES ('Good');
         WHEN 7 THEN
            INSERT INTO PUBLIC.CASE_TABLE (COL) VALUES ('Fair');
         WHEN 6 THEN
            INSERT INTO PUBLIC.CASE_TABLE (COL) VALUES ('Poor');
         ELSE
            INSERT INTO PUBLIC.CASE_TABLE (COL) VALUES ('No such grade');
      END CASE;
   END;
$$;

CALL caseExample1(6);
CALL caseExample1(4);
CALL caseExample1(10);

SELECT* FROM PUBLIC.CASE_TABLE;

Result

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

Searched Case

Teradata

Query

CREATE PROCEDURE caseExample2 ( grade NUMBER )
BEGIN
    CASE
        WHEN grade = 10 THEN INSERT INTO CASE_TABLE(COL) VALUES ('Excellent');
        WHEN grade = 9 THEN INSERT INTO CASE_TABLE(COL) VALUES ('Very Good');
        WHEN grade = 8 THEN INSERT INTO CASE_TABLE(COL) VALUES ('Good');
        WHEN grade = 7 THEN INSERT INTO CASE_TABLE(COL) VALUES ('Fair');
        WHEN grade = 6 THEN INSERT INTO CASE_TABLE(COL) VALUES ('Poor');
        ELSE INSERT INTO CASE_TABLE(COL) VALUES ('No such grade');
    END CASE;
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
$$
   BEGIN
      CASE
      	WHEN grade = 10 THEN
            INSERT INTO PUBLIC.CASE_TABLE (COL) VALUES ('Excellent');
         WHEN grade = 9 THEN
            INSERT INTO PUBLIC.CASE_TABLE (COL) VALUES ('Very Good');
         WHEN grade = 8 THEN
            INSERT INTO PUBLIC.CASE_TABLE (COL) VALUES ('Good');
         WHEN grade = 7 THEN
            INSERT INTO PUBLIC.CASE_TABLE (COL) VALUES ('Fair');
         WHEN grade = 6 THEN
            INSERT INTO PUBLIC.CASE_TABLE (COL) VALUES ('Poor');
         ELSE
            INSERT INTO PUBLIC.CASE_TABLE (COL) VALUES ('No such grade');
      END CASE;
   END;
$$;

CALL caseExample2(6);
CALL caseExample2(4);
CALL caseExample2(10);

SELECT * FROM PUBLIC.CASE_TABLE;

Result

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

Known Issues

No issues were found.

Related EWIs

No related EWIs.