IF

Translation reference to convert Teradata IF statement to Snowflake Scripting

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

Some parts in the output code are omitted for clarity reasons.

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

IN -> Teradata_01.sql

CREATE TABLE if_table(col1 varchar(30));

OUT -> Teradata_01.sql

CREATE OR REPLACE TABLE if_table (
col1 varchar(30))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
;

Possible IF variations

Teradata

IN -> Teradata_02.sql

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;

IN -> Teradata_03.sql

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;

IN -> Teradata_04.sql

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;

IN -> Teradata_05.sql

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

OUT -> Teradata_02.sql

CREATE OR REPLACE PROCEDURE ifExample1 (FLAG 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
      IF (:flag = 1) THEN
         INSERT INTO if_table (col1)
         VALUES ('one');
      END IF;
   END;
$$;

CALL ifExample1(1);

SELECT
   * FROM
   if_table;

OUT -> Teradata_03.sql

CREATE OR REPLACE PROCEDURE ifExample2 (FLAG 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
      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;

OUT -> Teradata_04.sql

CREATE OR REPLACE PROCEDURE ifExample3 (FLAG 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
      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;

OUT -> Teradata_05.sql

CREATE OR REPLACE PROCEDURE ifExample4 (FLAG 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
      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 |

Known Issues

No issues were found.

No related EWIs.

PreviousGET DIAGNOSTICS EXCEPTION NextLOCKING FOR ACCESS

Last updated 4 months ago