MSCEWI1067

UDF was transformed to Snowflake procedure, calling procedures inside a query is not supported.

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

Severity

High

Description

This error is added when a call to a UDF (user defined function) was found inside a query. Oracle UDFs and UDFs inside packages and some SQL Server UDFs, are being transformed to Snowflake Stored Procedures, which can not be called from a query.

The function is transformed to a Stored procedure to maintain functional equivalence and the function call is transformed to an empty Snowflake UDF function.

Example Code

Oracle

-- Create Function
CREATE OR REPLACE FUNCTION FUN1(PAR1 VARCHAR)
RETURN VARCHAR
IS
    VAR1 VARCHAR(20);
BEGIN
    SELECT COL1 INTO VAR1 FROM TABLE1 where col1 = 1;
    RETURN VAR1;
END;
/

--Function inside package
CREATE OR REPLACE PACKAGE BODY pkg1 AS
  FUNCTION f1(x NUMBER) RETURN NUMBER IS
  VAR1 NUMBER;
  BEGIN
    SELECT ID INTO VAR1 FROM TABLE1 WHERE ID = 1;
    RETURN 1;
  END f1;
END pkg1;
/

CREATE VIEW VIEW1 AS SELECT FUN1(COL2) FROM TABLE1;
CREATE VIEW VIEW2 AS SELECT PKG1.F1(COL1) FROM TABLE1;

SQL Server

CREATE OR ALTER FUNCTION PURCHASING.FOO()
RETURNS INT
AS
BEGIN
    DECLARE @i int = 0, @p int;
    Select @p = COUNT(*) FROM PURCHASING.VENDOR
    
    WHILE (@p < 1000)
    BEGIN
        SET @i = @i + 1
        SET @p = @p + @i
    END
        
    IF (@i = 6)
        RETURN 1
    
    RETURN @p
END

SELECT PURCHASING.FOO() as result

Output Code

CREATE OR REPLACE VIEW PUBLIC.VIEW1 AS SELECT
/*** MSC-ERROR - MSCEWI1067 - UDF WAS TRASFORMED TO SNOWFLAKE PROCEDURE, CALLING PROCEDURES INSIDE A QUERY IS NOT SUPPORTED ***/
PUBLIC.FUN1_UDF('FUN1(COL2)') FROM PUBLIC.TABLE1;

CREATE OR REPLACE VIEW PUBLIC.VIEW2 AS SELECT
/*** MSC-ERROR - MSCEWI1067 - UDF WAS TRASFORMED TO SNOWFLAKE PROCEDURE, CALLING PROCEDURES INSIDE A QUERY IS NOT SUPPORTED ***/
PUBLIC.PKG1.F1_UDF(COL1) FROM PUBLIC.TABLE1;

SELECT PURCHASING.FOO() /*** MSC-ERROR - MSCEWI1067 - UDF WAS TRASFORMED TO SNOWFLAKE PROCEDURE, CALLING PROCEDURES INSIDE A QUERY IS NOT SUPPORTED ***/ FROM PUBLIC.TABLE1;

Recommendations

PreviousMSCEWI1066 NextMSCEWI1068

Last updated 1 year ago

-- Create Function CREATE OR REPLACE FUNCTION FUN1(PAR1 VARCHAR) RETURN VARCHAR IS VAR1 VARCHAR(20); BEGIN SELECT COL1 INTO VAR1 FROM TABLE1 where col1 = 1; RETURN VAR1; END; / --Function inside package CREATE OR REPLACE PACKAGE BODY pkg1 AS FUNCTION f1(x NUMBER) RETURN NUMBER IS VAR1 NUMBER; BEGIN SELECT ID INTO VAR1 FROM TABLE1 WHERE ID = 1; RETURN 1; END f1; END pkg1; /

CREATE OR ALTER FUNCTION PURCHASING.FOO() RETURNS INT AS BEGIN DECLARE @i int = 0, @p int; Select @p = COUNT(*) FROM PURCHASING.VENDOR WHILE (@p < 1000) BEGIN SET @i = @i + 1 SET @p = @p + @i END IF (@i = 6) RETURN 1 RETURN @p END

CREATE OR REPLACE VIEW PUBLIC.VIEW1 AS SELECT /*** MSC-ERROR - MSCEWI1067 - UDF WAS TRASFORMED TO SNOWFLAKE PROCEDURE, CALLING PROCEDURES INSIDE A QUERY IS NOT SUPPORTED ***/ PUBLIC.FUN1_UDF('FUN1(COL2)') FROM PUBLIC.TABLE1; CREATE OR REPLACE VIEW PUBLIC.VIEW2 AS SELECT /*** MSC-ERROR - MSCEWI1067 - UDF WAS TRASFORMED TO SNOWFLAKE PROCEDURE, CALLING PROCEDURES INSIDE A QUERY IS NOT SUPPORTED ***/ PUBLIC.PKG1.F1_UDF(COL1) FROM PUBLIC.TABLE1;