With Modifier

Select statement that uses the WITH modifier with a list of several named queries also known as common table expressions (CTEs).

See With Modifier

Snowflake supports Teradata's WITH modifier on a SELECT statement that has several CTEs (Common Table Expressions). Teradata supports any order of CTE definition, regardless of whether it is referenced before it is declared or not, but Snowflake requires that if a CTE calls another CTE, it must be defined before it is called. Then the converted sequence of CTEs within the WITH will be reordered into the unreferenced CTEs, then the CTE that calls the next CTE, and so on.

Where there is a cycle detected in the WITH calling sequence, it will be left as the original, without any changes to the sequence as detailed in an example of the MSCEWI1077.

In the example below, there are two CTEs named n1 and n2, the n1 referring to n2. Then the n2 must be defined first in Snowflake as the corresponding converted code.

Teradata

With Modifier

with recursive n1(c1) as (select c1, c3 from t2, n1),
     n2(c2) as (select c2 from tablex)
     SELECT * FROM t1;

Snowflake

With Modifier

WITH RECURSIVE t2(c2) AS
(
   SELECT c2 FROM PUBLIC.tablex
),
t1(c1) AS
(
   SELECT c1, c3 FROM t2
)
SELECT * FROM t1;

Known Issues

1. Impossible to reorder when cycles were found

When the CTEs references are analyzed and there is a cycle between the calls of the CTEs, the CTEs will not be ordered.

Related EWIs

1. MSEWI1077 - Cycle found between CTE calls. CTEs cannot be ordered.