IF

Translation reference to convert Teradata IF statement to Snowflake Scripting

Description

Provides conditional execution based on the truth value of a condition.

For more information regarding Teradata IF, check here.

IF conditional_expression THEN
     statement
     [ statement ]... 
[ ELSEIF conditional_expression THEN
     statement
     [ statement ]... ]...
[ ELSE   
     statement
     [ statement ]... ]
END IF;

Sample Source Patterns

Sample auxiliar table

Teradata

CREATE TABLE if_table(col1 varchar(30));

Snowflake

CREATE TABLE PUBLIC.if_table (col1 varchar(30));

Possible IF variations

Teradata

Code 1

CREATE PROCEDURE ifExample1 ( flag NUMBER )
BEGIN
   IF flag = 1 THEN
      INSERT INTO if_table(col1) VALUES ('one');
   END IF;
END;

CALL ifExample1(1);
SELECT * FROM if_table;

Code 2

CREATE PROCEDURE ifExample2 ( flag NUMBER )
BEGIN
   IF flag = 1 THEN
      INSERT INTO if_table(col1) VALUES ('one');
   ELSE
      INSERT INTO if_table(col1) VALUES ('Unexpected input.');
   END IF;
END;

CALL ifExample2(2);
SELECT * FROM if_table;

Code 3

CREATE PROCEDURE ifExample3 ( flag NUMBER )
BEGIN
   IF flag = 1 THEN
      INSERT INTO if_table(col1) VALUES ('one');
   ELSEIF flag = 2 THEN
      INSERT INTO if_table(col1) VALUES ('two');
   ELSEIF flag = 3 THEN
      INSERT INTO if_table(col1) VALUES ('three');
   END IF;
END;

CALL ifExample3(3);
SELECT * FROM if_table;

Code 4

CREATE PROCEDURE ifExample4 ( flag NUMBER )
BEGIN
   IF flag = 1 THEN
      INSERT INTO if_table(col1) VALUES ('one');
   ELSEIF flag = 2 THEN
      INSERT INTO if_table(col1) VALUES ('two');
   ELSEIF flag = 3 THEN
      INSERT INTO if_table(col1) VALUES ('three');
   ELSE
      INSERT INTO if_table(col1) VALUES ('Unexpected input.');  
   END IF;
END;

CALL ifExample4(4);
SELECT * FROM if_table;

Result 1

|COL1|
|----|
|one |

Result 2

|COL1             |
|-----------------|
|Unexpected input.|

Result 3

|COL1 |
|-----|
|three|

Result 4

|COL1             |
|-----------------|
|Unexpected input.|

Snowflake Scripting

Query 1

CREATE OR REPLACE PROCEDURE PUBLIC.ifExample1 (FLAG FLOAT )
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   BEGIN
      IF ( flag = 1) THEN
         INSERT INTO PUBLIC.if_table (col1) VALUES ('one');
      END IF;
   END;
$$;

CALL ifExample1(1);
SELECT * FROM PUBLIC.if_table;

Query 2

CREATE OR REPLACE PROCEDURE PUBLIC.ifExample2 (FLAG FLOAT )
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   BEGIN
      IF ( flag = 1) THEN
         INSERT INTO PUBLIC.if_table (col1) VALUES ('one');
      ELSE
         INSERT INTO PUBLIC.if_table (col1) VALUES ('Unexpected input.');
      END IF;
   END;
$$;

CALL ifExample2(2);
SELECT * FROM PUBLIC.if_table;

Query 3

CREATE OR REPLACE PROCEDURE PUBLIC.ifExample3 (FLAG FLOAT )
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   BEGIN
      IF ( flag = 1) THEN
         INSERT INTO PUBLIC.if_table (col1) VALUES ('one');
      ELSEIF ( flag = 2) THEN
         INSERT INTO PUBLIC.if_table (col1) VALUES ('two');
      ELSEIF ( flag = 3) THEN
         INSERT INTO PUBLIC.if_table (col1) VALUES ('three');
      END IF;
   END;
$$;

CALL ifExample3(3);
SELECT * FROM PUBLIC.if_table;

Query 4

CREATE OR REPLACE PROCEDURE PUBLIC.ifExample4 (FLAG FLOAT )
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   BEGIN
      IF ( flag = 1) THEN
         INSERT INTO PUBLIC.if_table (col1) VALUES ('one');
      ELSEIF ( flag = 2) THEN
         INSERT INTO PUBLIC.if_table (col1) VALUES ('two');
      ELSEIF ( flag = 3) THEN
         INSERT INTO PUBLIC.if_table (col1) VALUES ('three');
      ELSE
         INSERT INTO PUBLIC.if_table (col1) VALUES ('Unexpected input.');
      END IF;
   END;
$$;

CALL ifExample4(4);
SELECT * FROM PUBLIC.if_table;

Result 1

|COL1|
|----|
|one |

Result 2

|COL1             |
|-----------------|
|Unexpected input.|

Result 3

|COL1 |
|-----|
|three|

Result 4

|COL1             |
|-----------------|
|Unexpected input.|

Known issues

No issues were found.

Related EWIs

No related EWIs.