MSCEWI4053

Multiple selects in if body were partially transformed.

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

Severity

High

Description

This EWI is added to one or more resulting CTEs of a UDF transformation that generates a Snowflake function, when the IF statement that was the source for such CTE only contained variable assignments using the SELECT @local_variable syntax.

Even though this EWI's severity is set to high, the basic scenarios like the one from the code example are proven to work as intended, however depending on the complexity of each individual SELECT statement, and the overall amount of statements inside the IF's BEGIN ... END block, the functional equivalence of the result compared to the input code cannot be guaranteed.

Code Example

Input Code:

CREATE or ALTER FUNCTION PURCHASING.SELECTINUDF (
    @param1 varchar(12)
)
RETURNS int
AS
BEGIN
    declare @var1 int;
    declare @var2 int;
    declare @var3 int;

    IF @param1 = 'first'
    BEGIN
        select @var1 = col1 + 10 from table1 WHERE id = 0;
        select @var2 = col1 + 20 from table1 WHERE id = 0;
        select @var3 = col1 + 30 from table1 WHERE id = 0;
    END

    RETURN @var1
END

Output Code:

/*** MSC-WARNING - MSCEWI1069 - USER DEFINED FUNCTION INNER LOGIC WAS TRANSFORMED TO A SINGLE COMMON TABLE EXPRESSION ***/
CREATE OR REPLACE FUNCTION PURCHASING.SELECTINUDF (PARAM1 STRING)
RETURNS INT
LANGUAGE SQL
AS
$$
    WITH
    --** MSC-ERROR - MSCEWI4053 - ONE OR MORE SELECT STATEMENTS INSIDE THIS CONDITIONAL STATEMENT WERE PARTIALLY TRANSFORMED. FUNCTIONAL EQUIVALENCE CANNOT BE GUARANTEED **
    CTE1 AS
    (
        SELECT
            CASE
                WHEN PARAM1 = 'first'
                    THEN (SELECT
                        col1 + 10 AS VAR1 from
                        PUBLIC.table1
                        WHERE
                        id = 0)
            END AS VAR1,
            CASE
                WHEN PARAM1 = 'first'
                        THEN (SELECT
                        col1 + 20 AS VAR2 from
                        PUBLIC.table1
                        WHERE
                        id = 0)
            END AS VAR2,
            CASE
                WHEN PARAM1 = 'first'
                        THEN (SELECT
                        col1 + 30 AS VAR3 from
                        PUBLIC.table1
                        WHERE
                        id = 0)
            END AS VAR3
    )
    SELECT
        VAR1
    FROM
        CTE1
$$;

Recommendations

• If you need more support, you can email us at [email protected]