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 SSC-EWI-TD0077.

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.

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

Teradata

With Modifier

IN -> Teradata_01.sql

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

Snowflake

With Modifier

OUT -> Teradata_01.sql

WITH RECURSIVE n1(c1) AS
(
     SELECT
          c1,
          c3 from
          t2, n1
),
n2(c2) AS
(
     SELECT
          c2 from
          tablex
)
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

No related EWIs.