CASE
Translation reference to convert Teradata CASE statement to Snowflake Scripting
Last updated
Translation reference to convert Teradata CASE statement to Snowflake Scripting
Last updated
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 .
-- 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;
CREATE TABLE case_table(col varchar(30));
CREATE OR REPLACE TABLE case_table (
col varchar(30))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
;
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;
|COL |
|-------------|
|Poor |
|No such grade|
|Excellent |
CREATE OR REPLACE PROCEDURE caseExample1 (GRADE NUMBER(38, 18))
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
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;
|COL |
|-------------|
|Poor |
|No such grade|
|Excellent |
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;
|COL |
|-------------|
|Poor |
|No such grade|
|Excellent |
CREATE OR REPLACE PROCEDURE caseExample2 (GRADE NUMBER(38, 18))
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
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;
|COL |
|-------------|
|Poor |
|No such grade|
|Excellent |
No issues were found.
No related EWIs.