Recursion using WITH

I am trying to write a query where
with tmp_tree(
from tree A
where parent= ‘A’ and
A.child !=A.parent
union all
from tree A ,tmp_tree tmp
and A.ParentNodeId=‘A’
and tmp.child = A.child
and A.child !=A.parent
and A.level > tmp.level
select *
from tmp_tree

table tmp_tree not found

Is it that - Dremio does not support recursion - is there some other way to get this dataset ?

Do you have tmp_tree already in Dremio as a PDS or VDS? Sometimes if not you might need the full path to it Like Source.folder.file

No i dont have this as PDS or VDS … i wrote this as a like to like migration of SQL to Dremio
Do i have to create a PDS\VDS for this to work im Dremio ?

Well normally you add a source first. i.e. like Mongo, then you’ll see the collections within that source and can promote it to a physical dataset (PDS) run queries there and save as a VDS from there.

Can you provide a screenshot of how your source is setup?

I think the current conversation here is a red herring. The quick answer is that Dremio does not currently support recursive CTE.

i see so - whatever however i try i cannot achieve it ?
If i create a tmp_tree vds with the above query and then use it ?

What you are trying to do will not work because it is not supported.

Still learning here as well. Can this be accomplished via CREATE TABLE AS $scratch.tmp_tree …, and then querying the $scratch table?

@Jbrezovan What is the SQL you are using behind the CTAS?

I started here: Dremio

Make sure you enable exports for the sources (not tables, but sources)–I missed this detail, and kept getting errors like “[x source] is immutable for user” until I read the documentation more closely and figured it out.