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 HERE.
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;
/
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
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;
Recommendations
If you need more support, you can email us at [email protected]
Last updated