3

I have several tables in Postgres that we are migrating to Snowflake. Some of these table are hierarchical (a foreign key point to the same table), normally to query this data I use CTE tables and/or recursive SQL statements.

As far as I can tell Snowflake does not support these operations. Are there any other queries that can be made to mimic this or work around this?

"Currently, Snowflake does not fully support common table expressions (CTE) in DDL operations."

Snowflake documentation: https://docs.snowflake.net/manuals/sql-reference/constructs/with.html

see: https://www.postgresql.org/docs/current/static/queries-with.html

  • @ajputnam the very link you provide says that Snowflake supports CTEs. – ypercubeᵀᴹ Sep 12 at 8:40
  • 1
    "DDL" means Data Definition Language. I don't think you would need a recursive CTE in a CREATE or ALTER TABLE statement. – ypercubeᵀᴹ Sep 12 at 8:43
3

Snowflake currently supports recursive CTEs.

I just build an sql to parse an account tree to consolidate amounts at the branch level.

From the source account tree data, main is used to build each branch & immediate sub branch/leaves combination

with main as (
  select a.scode as src , b.scode as tgt  
  from accttree a 
    join accttree b on (a.totalinto=b.scode)
), tree as (
  select src, NULL as bridge, tgt  
  from main 
  --where hprop = 15365 
  union all 
  select m.src, m.tgt as bridge, t.tgt as tgt 
  from main m 
    join tree t on (m.tgt = t.src)
)
--Tree gives the branch and leaf relation for the entire set of branches
Select * 
from tree

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.