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

CREATE TABLE if_table(col1 varchar(30));

Possible IF variations

Teradata

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;

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;

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;

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;

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

Snowflake Scripting

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;

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;

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;

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;

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

Known issues

No issues were found.

No related EWIs.

PreviousGET DIAGNOSTICS EXCEPTION NextLOOP

Last updated 3 years ago

Was this helpful?