Comment on page
MSCEWI1069
User defined function inner logic was transformed to a single Common Table Expression
Low
Snowflake user defined functions do not support multiple statements like
DECLARE, SET, IF/ELSE, etc in their bodies. The approach of using a single COMMON TABLE EXPRESSION, helps to keep the functional equivalence of the original user-defined function and preserve its functionallity when it is called inside queries.CREATE OR ALTER FUNCTION PURCHASING.HasActiveFlag(@BusinessEntityID int)
RETURNS VARCHAR(10) AS
BEGIN
DECLARE @result VARCHAR(10)
DECLARE @ActiveFlag BIT
SELECT @ActiveFlag = ActiveFlag from PURCHASING.VENDOR v where v.BUSINESSENTITYID = @BusinessEntityID
IF @ActiveFlag = 1 SET @result = 'YES'
ELSE IF @ActiveFlag = 0 SET @result = 'NO'
RETURN @result
END
/*** MSC-WARNING - MSCEWI1069 - USER DEFINED FUNCTION INNER LOGIC WAS TRANSFORMED TO A SINGLE COMMON TABLE EXPRESSION ***/
CREATE OR REPLACE FUNCTION PURCHASING.HasActiveFlag (
BusinessEntityID int
)
RETURNS VARCHAR(10)
AS
$$
WITH
CTE1 as (SELECT ActiveFlag AS ActiveFlag
from PURCHASING.VENDOR v
where v.BUSINESSENTITYID = BusinessEntityID),
CTE2 as (SELECT CASE
WHEN (SELECT ActiveFlag from CTE1) = 1 THEN 'YES'
WHEN (SELECT ActiveFlag from CTE1) = 0 THEN 'NO'
end as result)
SELECT result FROM CTE2
$$;