SSC-EWI-TS0010

Common table expression in view not supported in Snowflake.

Severity

High

Some parts of the output code are omitted for clarity reasons.

Description

This error is added when an invalid CTE is inside a view, since views are materialized representations of queries, which means that they only define how data is retrieved and presented, not how it is manipulated.

Code Example

Input Code:

IN -> SqlServer_01.sql

CREATE VIEW EmployeeInsertVew
AS
WITH MyCTE AS ( SELECT NationalIDNumber from [HumanResources].[Employee] 
UNION ALL 
SELECT BusinessEntityID FROM HumanResources.EmployeeDepartmentHistory )
INSERT INTO EmployeeTable (SELECT * FROM MyCTE);

Output Code:

OUT -> SqlServer_01.sql

!!!RESOLVE EWI!!! /*** SSC-EWI-TS0010 - COMMON TABLE EXPRESSION IN VIEW NOT SUPPORTED IN SNOWFLAKE. ***/!!!
--** SSC-FDM-0007 - MISSING DEPENDENT OBJECTS "[HumanResources].[Employee]", "HumanResources.EmployeeDepartmentHistory", "EmployeeTable" **
CREATE OR REPLACE VIEW EmployeeInsertVew
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
--!!!RESOLVE EWI!!! /*** SSC-EWI-0013 - EXCEPTION THROWN WHILE CONVERTING ITEM: Mobilize.AnsiSql.AST.SqlWithCte. LINE: 3 OF FILE: /Users/iclavijobermudez/Documents/Workspace/snowflake-mountain/SC.SnowConvert/Tools/DocVerifier/out/temp/IN/SqlServer_01.sql ***/!!!
--WITH MyCTE AS ( SELECT
--NationalIDNumber
--from
--HumanResources.Employee
--UNION ALL
--SELECT
--BusinessEntityID
--FROM
--HumanResources.EmployeeDepartmentHistory
--)
--INSERT INTO EmployeeTable (
--SELECT
--*
--FROM
--MyCTE AS MyCTE)
               ;

Related EWI

1. SSC-EWI-0013: This error appears when an exception is raised while converting an item from the source code.

Recommendations

• If you need more support, you can email us at [e-mail]