IF

Translation reference for IF structure

Description

The IF statement either runs or skips a sequence of one or more statements, depending on the value of a BOOLEAN expression. For more information regarding Oracle IF, check here.

Oracle IF Syntax

IF boolean_expression THEN 
    statement 
    [ statement ]...
[ 
ELSIF boolean_expression THEN 
    statement 
    [ statement ]... ]...
   [ 
ELSE 
statement [ statement ]... ] END IF ;

Snowflake Scripting IF Syntax

IF ( <condition> ) THEN
    <statement>;
    [ <statement>; ... ]
[
ELSEIF ( <condition> ) THEN
    <statement>;
    [ <statement>; ... ]
]
[
ELSE
    <statement>;
    [ <statement>; ... ]
]
END IF;

Sample Source Patterns

Sample auxiliar table

Oracle

CREATE TABLE if_table(col1 varchar(30));

Snowflake

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

Possible IF variations

Oracle

Code 1

IN -> Oracle_01.sql

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

CALL ifExample1(1);
SELECT * FROM if_table;

Code 2

IN -> Oracle_02.sql

CREATE OR REPLACE PROCEDURE ifExample2 ( flag NUMBER )
IS
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

IN -> Oracle_03.sql

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

CALL ifExample3(3);
SELECT * FROM if_table;

Code 4

IN -> Oracle_04.sql

CREATE OR REPLACE PROCEDURE ifExample4 ( flag NUMBER )
IS
BEGIN
    IF flag = 1 THEN
        INSERT INTO if_table(col1) VALUES ('one');
    ELSIF flag = 2 THEN
        INSERT INTO if_table(col1) VALUES ('two');
    ELSIF 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

OUT -> Oracle_01.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":"oracle"}}'
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;

Query 2

OUT -> Oracle_02.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":"oracle"}}'
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;

Query 3

OUT -> Oracle_03.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":"oracle"}}'
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;

Query 4

OUT -> Oracle_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":"oracle"}}'
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;

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.