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

    with recursive

    user566095

      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

      Capture.PNG

        • 1. Re: with recursive
          John Thorton

          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

          Capture.PNG

          Problem & solution have NOTHING to do with Oracle .

           

          Please mark this thread  as  ANSWERED & post to URL below

           

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

          • 2. Re: with recursive
            Frank Kulash

            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

            Capture.PNG

            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
              mathguy

              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
                mathguy

                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
                  Paulzip

                  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
                    user566095

                    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
                      mathguy

                      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
                        Frank Kulash

                        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.