MSCINF0008

Unable to generate correct RETURNS TABLE clause due to missing dependent object information.

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

Severity

None

Description

Snowflake requires a valid RETURNS TABLE clause for CREATE TABLE FUNCTION statements.

SnowConvert may encounter scenarios where the original BigQuery source code does not have a RETURNS TABLE clause, and has to build a new one from the ground up. To do this, an analysis is made to the CREATE TABLE FUNCTION query in order to properly infer the types of the columns of the resulting table. When SnowConvert cannot gather the required information, this EWI is added.

Code Example

BigQuery

CREATE OR REPLACE TABLE FUNCTION function_name_noreturns_asterisk_join (parameter_name INTEGER)
AS
  SELECT * FROM unknownTable1 t1 JOIN unknownTable2 t2 ON t1.col1 = t2.fk_col1;

Snowflake

--** MSC-WARNING - MSCEWI1050 - MISSING DEPENDENT OBJECTS "unknownTable1", "unknownTable2" **
CREATE OR REPLACE FUNCTION function_name_noreturns_asterisk_join (parameter_name INTEGER)
----** MSC-INFORMATION - MSCINF0008 - UNABLE TO GENERATE CORRECT RETURNS TABLE CLAUSE DUE TO MISSING DEPENDENT OBJECT INFORMATION. **
--RETURNS TABLE (
--)
AS
  $$
      SELECT * FROM
        unknownTable1 t1 JOIN
          unknownTable2 t2 ON t1.col1 = t2.fk_col1
  $$;

Recommendations

• Always try to include any dependent object definitions in the input code, so that SnowConvert has access to important information.

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