8 Replies Latest reply on Jan 20, 2019 6:43 PM by Frank Kulash

# with recursive

As I am writing query in Snowflake  and it doesnt support recursive with clause ,

Can some one help me how to achieve below in Snowflake query without recursive

1. WITH RECURSIVE RECURTEMP(ID,KCODE,LVL)
2. AS(SELECT ID, MIN(KCODE) AS KCODE,1
3.   FROM TABLE_A
4.   GROUP BY 1
5.   UNION ALL
6.   SELECT b.ID, trim(a.KCODE)|| ';'||trim(b.KCODE), LVL+1
7.   FROM TABLE_A a
8.   INNER JOIN RECURTEMP b ON a.ID = b.ID AND a.KCODE > b.KCODE
9.   )
10. SELECT * FROM RECURTEMP

• ###### 1. Re: with recursive

user566095 wrote:

As I am writing query in Snowflake and it doesnt support recursive with clause ,

Can some one help me how to achieve below in Snowflake query without recursive

1. WITH RECURSIVE RECURTEMP(ID,KCODE,LVL)
2. AS(SELECT ID, MIN(KCODE) AS KCODE,1
3. FROM TABLE_A
4. GROUP BY 1
5. UNION ALL
6. SELECT b.ID, trim(a.KCODE)|| ';'||trim(b.KCODE), LVL+1
7. FROM TABLE_A a
8. INNER JOIN RECURTEMP b ON a.ID = b.ID AND a.KCODE > b.KCODE
9. )
10. SELECT * FROM RECURTEMP

Problem & solution have NOTHING to do with Oracle .

https://support.snowflake.net/s/

• ###### 2. Re: with recursive

Hi,

Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables involved, so that the people who want to help you can re-create the problem and test their ideas.
Also post the exact results you want from that data, and an explanation of how you get those results from that data, with specific examples.

Always say which version of Oracle you're using (for example, 12.2.0.1.0).
See the Forum FAQ: Re: 2. How do I ask a question on the forums?

user566095 wrote:

As I am writing query in Snowflake and it doesnt support recursive with clause ,

Can some one help me how to achieve below in Snowflake query without recursive

1. WITH RECURSIVE RECURTEMP(ID,KCODE,LVL)
2. AS(SELECT ID, MIN(KCODE) AS KCODE,1
3. FROM TABLE_A
4. GROUP BY 1
5. UNION ALL
6. SELECT b.ID, trim(a.KCODE)|| ';'||trim(b.KCODE), LVL+1
7. FROM TABLE_A a
8. INNER JOIN RECURTEMP b ON a.ID = b.ID AND a.KCODE > b.KCODE
9. )
10. SELECT * FROM RECURTEMP

Sorry, I don't know anything about Snowflake.

It looks like you could get those results using CONNECT BY, or using LISTAGG.  Does Snowflake support them?

Perhaps you can create a view in the database, and then have Snowflake query the view.

• ###### 3. Re: with recursive

with

table_a as (

select 'ABCD' as id, 'K10' as kcode from dual union all

select 'ABCD'      , 'K53'          from dual union all

select 'ABCD'      , 'K55'          from dual union all

select 'ABCD'      , 'K56'          from dual

)

select     id, ltrim(sys_connect_by_path(kcode, ';'), ';') as kcode, level as lvl

from       table_a

where      connect_by_isleaf = 1

connect by kcode < prior kcode

and id = prior id

and prior sys_guid() is not null

order by   lvl, kcode

;

ID   KCODE                            LVL

---- ------------------------- ----------

ABCD K10                                1

ABCD K53;K10                            2

ABCD K55;K10                            2

ABCD K56;K10                            2

ABCD K55;K53;K10                        3

ABCD K56;K53;K10                        3

ABCD K56;K55;K10                        3

ABCD K56;K55;K53;K10                    4

• ###### 4. Re: with recursive

LOL... Wait, since you posted in an Oracle forum, I assumed Snowflake is built on top of Oracle. Is it? If it is not, what's the point of posting in this forum?

Neither CONNECT BY queries nor LISTAGG are in the SQL Standard (as far as I know); they are proprietary to Oracle, so they will not help.  EDIT: I just took a quick look at the Snowflake documentation. It states that it does not support recursive WITH. It DOES have a LISTAGG function. However, that by itself won't help; you still need to be able to create the lists on which to use LISTAGG, and that requires a form of recursion to begin with. END EDIT

If Snowflake is a competing database product with its own SQL dialect, it's a pretty weak one if it does not support recursive WITH clause, which IS part of the SQL Standard, and has been for a while.

If that's the case, you better find out what tools Snowflake does offer, and use those. If you know ahead of time an upper bound for the number of levels, you could do "by hand" what the recursive WITH clause does - write the query as a repeated self-join; but that does not work without an a priori upper bound on the number of levels. (Which in your example is the same as the number of rows in the input table.)

• ###### 5. Re: with recursive

Wrong forum, this is an Oracle forum.

FYI, Snowflake DOES NOT support recursive queries, so you cannot do what you are trying to do unless you do it client side with something like Python.

• ###### 6. Re: with recursive

I agree that Snowflake doesnt support and i am trying to see how to achieve without Recursive function or Connect by .. There should be some before these features came to Oracle

I am also trying to see how to achieve using python too

• ###### 7. Re: with recursive

Well - do you know beforehand that there will be at most four levels (or some other upper bound KNOWN IN ADVANCE, before seeing the data)? If not, then you do need some kind of recursion. Beyond that, you are asking what's available in Snowflake, on an Oracle forum. Does that make sense to you? Find out if there is a Snowflake forum, and ask there.

• ###### 8. Re: with recursive

Hi,

user566095 wrote:

I agree that Snowflake doesnt support and i am trying to see how to achieve without Recursive function or Connect by .. There should be some before these features came to Oracle

...

Actually, CONNECT BY was a feature in the first commercially available version of Oracle, in 1979.