Comment on page
MSCEWI4053
Multiple selects in if body were partially transformed.
High
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.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
/*** 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
$$;