MSCEWI4034

RETURNS clause incomplete due to missing symbols

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

Severity

High

Description

This EWI is added to the output code when the RETURNS TABLE clause of a CREATE FUNCTION could not be properly generated. This happens when the columns that must be specified in the resulting RETURNS TABLE clause cannot be inferred by SnowConvert, thus leaving the RETURNS TABLE clause empty.

Code Example

Input Code:

CREATE FUNCTION Sales.SalesFN()
RETURNS TABLE
AS
RETURN
(
  WITH CTE AS (
  SELECT DepartmentID, Name, GroupName
  FROM HumanResources.Department
  )
  SELECT tab.* FROM CTE tab
);

Output Code:

CREATE OR REPLACE FUNCTION Sales.SalesFN()
RETURNS /*** MSC-ERROR - MSCEWI4034 - RETURNS CLAUSE INCOMPLETE. SYMBOLS FOR CREATING THE TABLE COLUMNS COULD NOT BE FOUND. ***/
 TABLE(

)
AS
$$
  /*** MSC-WARNING - MSCINF0017 - PERFORMANCE WARNING - RECURSION FOR CTE NOT CHECKED. MIGHT REQUIRE RECURSIVE KEYWORD ***/
  WITH CTE AS (
    SELECT DepartmentID,
    Name,
    GroupName
    FROM HumanResources.Department
  )
  SELECT tab.* FROM PUBLIC.CTE tab
$$;

Recommendations

• The causes for this issue may vary. Be sure to include all the objects that your code needs. If the issue persists even though the migration has access to all the necessary objects, please do contact us with information about your specific scenario.

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