Common Table Expression (CTE)

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

Common table expressions are supported in Snowflake SQL by default.

Snowflake SQL syntax

Subquery:

[ WITH
       <cte_name1> [ ( <cte_column_list> ) ] AS ( SELECT ...  )
   [ , <cte_name2> [ ( <cte_column_list> ) ] AS ( SELECT ...  ) ]
   [ , <cte_nameN> [ ( <cte_column_list> ) ] AS ( SELECT ...  ) ]
]
SELECT ...

Recursive CTE:

[ WITH [ RECURSIVE ]
       <cte_name1> ( <cte_column_list> ) AS ( anchorClause UNION ALL recursiveClause )
   [ , <cte_name2> ( <cte_column_list> ) AS ( anchorClause UNION ALL recursiveClause ) ]
   [ , <cte_nameN> ( <cte_column_list> ) AS ( anchorClause UNION ALL recursiveClause ) ]
]
SELECT ...

Where:

anchorClause ::=
    SELECT <anchor_column_list> FROM ...

recursiveClause ::=
    SELECT <recursive_column_list> FROM ... [ JOIN ... ]

Noteworthy details

The RECURSIVE keyword does not exist in T-SQL, and the transformation does not actively add the keyword to the result. A warning is added to the output code in order to state this behavior.

Common Table Expression with SELECT INTO

The following transformation occurs when the WITH expression is followed by an SELECT INTO statement and it will be transformed into a TEMPORARY TABLE.

SQL Server:

IN -> SqlServer_01.sql

WITH ctetable(col1, col2) AS
    (
        SELECT	col1, col2 FROM	t1 poh WHERE poh.col1 = 16 and poh.col2 = 4
    ),
    employeeCte AS
    (
	SELECT BUSINESSENTITYID, VACATIONHOURS FROM employee WHERE BUSINESSENTITYID = (SELECT col1 FROM ctetable)
    ),
    finalCte AS
    (
        SELECT BUSINESSENTITYID, VACATIONHOURS FROM employeeCte  
    ) SELECT * INTO #table2 FROM finalCte;

SELECT * FROM #table2;

Snowflake:

OUT -> SqlServer_01.sql

CREATE OR REPLACE TEMPORARY TABLE T_table2 AS
	WITH ctetable (
		col1,
		col2
	) AS
		   (
		       SELECT
		   		col1,
		   		col2
		       FROM
		   		t1 poh
		       WHERE
		   		poh.col1 = 16 and poh.col2 = 4
		   ),
		   		employeeCte AS
		   		    (
		   			SELECT
		   		BUSINESSENTITYID,
		   		VACATIONHOURS
		       FROM
		   		employee
		       WHERE
		   		BUSINESSENTITYID =
 		   		                  !!!RESOLVE EWI!!! /*** SSC-EWI-0108 - THE FOLLOWING SUBQUERY MATCHES AT LEAST ONE OF THE PATTERNS CONSIDERED INVALID AND MAY PRODUCE COMPILATION ERRORS ***/!!! (SELECT
 		   		                  		col1
 		   		                  	FROM
 		   		                  		ctetable
 		   		                  )
		   		    ),
		   		finalCte AS
		   		    (
		   		        SELECT
		   		BUSINESSENTITYID,
		   		VACATIONHOURS
		       FROM
		   		employeeCte
		   		    )
		   		SELECT
		       *
		       FROM
		       finalCte;

		       SELECT
		       *
		       FROM
		       T_table2;

Common Table Expression with other expressions

The following transformation occurs when the WITH expression is followed by INSERT or DELETE statements.

SQL Server:

IN -> SqlServer_02.sql

WITH CTE AS( SELECT * from table1)
INSERT INTO Table2 (a,b,c,d)
SELECT a,b,c,d
FROM CTE
WHERE e IS NOT NULL;

Snowflake:

OUT -> SqlServer_02.sql

INSERT INTO Table2 (a, b, c, d)
WITH CTE AS( SELECT
*
from
table1
)
SELECT
a,
b,
c,
d
FROM
CTE AS CTE