SSC-FDM-OR0021

For Loop With Float Number As Bound May Not Behave Correctly In Snowflake Scripting

Description

Snowflake Scripting only allows an INTEGER or an expression that evaluates to an INTEGER as a bound for the FOR LOOP condition. Floating numbers will be rounded up or down and alter the original bound.

The lower bound will be rounded to the closest integer number. For example:

3.1 -> 3, 6.7 -> 7, 4.5 -> 5

However the upper bound will be truncated to the closest lower integer. For example:

3.1 -> 3, 6.7 -> 6, 4.5 -> 4

Snowflake Scripting

CREATE OR REPLACE PROCEDURE p1()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
    DECLARE
        var1 VARCHAR DEFAULT '';
        var2 VARCHAR DEFAULT '';
        var3 VARCHAR DEFAULT '';
    BEGIN
        --Loop 1
        FOR i IN 1.2 TO 5.2 DO
            var1 := var1 || ' ' || i::VARCHAR;
        END FOR;
        
        --Loop 2
        FOR i IN 1.7 TO 5.5 DO
            var2 := var2 || ' ' || i::VARCHAR;
        END FOR;
        
        --Loop 3
        FOR i IN 1.5 TO 5.8 DO
            var3 := var3 || ' ' || i::VARCHAR;
        END FOR;
        RETURN  ' Loop1: ' || var1 ||
                ' Loop2: ' || var2 ||
                ' Loop3: ' || var3;
    END;
$$;

CALL p1();

Result

P1                                                |
--------------------------------------------------+
 Loop1:  1 2 3 4 5                                |
 Loop2:  2 3 4 5                                  |
 Loop3:  2 3 4 5                                  |

Example Code

Input Code:

IN -> Oracle_01.sql

CREATE OR REPLACE PROCEDURE p1
AS
BEGIN
FOR i NUMBER(5,1) IN 1.2 .. 5.7 LOOP
    NULL;
END LOOP;
END;

Output Code:

OUT -> Oracle_01.sql

CREATE OR REPLACE PROCEDURE p1 ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        --** SSC-FDM-OR0021 - FOR LOOP WITH FLOAT NUMBER AS LOWER OR UPPER BOUND MAY NOT BEHAVE CORRECTLY IN SNOWFLAKE SCRIPTING **
        FOR i IN 1.2 TO 5.7 LOOP
            NULL;
        END LOOP;
    END;
$$;

Recommendations

• Rewrite the FOR LOOP condition so it uses integers.

• If you need more support, you can email us at [e-mail]