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.
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
Snowflake
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.
Last updated