4 Replies Latest reply on Nov 26, 2013 7:55 PM by Frank Kulash

    Can I use the Connect By operator to generate a set?

    user576036

      The Connect By operator is a way to handle hierarchical models implemented through tables.

      Is it possible to use it generate sets like in CTE (or recursive subquery factoring)?

        • 1. Re: Can I use the Connect By operator to generate a set?
          Frank Kulash

          Hi,

           

          Sorry, it's unclear what you mean.

          Any query (whether it uses CONNECT BY, a recursive WITH clause, both or neither) produces a result set.  In that sense, Yes, CONNECT BY can generate sets.

           

          A recursive WITH clause can do anything that CONNECT BY can do, and more.  For certain problems, especially those involving tree structures where you have parent_id and child_id columns, CONNECT BY is easier to debug and use.

           

          It would help if you could post a specific question.  For example, "Using the scott.emp table, I have this recursive WITH query ...  that does this ...  I'm looking for different ways to get the same reults because ... Would CONNECT BY help?  How can I get the same results using CONNECT BY?"

          1 person found this helpful
          • 2. Re: Can I use the Connect By operator to generate a set?
            gaverill

            I don't have a good example for you, but you should be able to combine a hierarchical query and a recursive CTE like so to generate a set of, e.g. test data:

             

            with    Generate_Input
            as    (
                select    ...
                    from    DUAL
                    connect by
                        level <= :n
                    )
            ,    Recursive_CTE(...)
            as    (
                select    ...
                    from    Generate_Input i
                    union    all
                select    ...
                from    Generate_Input i
                join    Recursive_CTE r on
                        ...
                )
            select    ...
            from    Recursive_CTE
            ;
            

             

            FWIW...

             

            Gerard

            1 person found this helpful
            • 3. Re: Can I use the Connect By operator to generate a set?
              user576036

              Sorry, I should have made myself more clear..

               

              The Connect By is using with hierarchical tables: we take such a table, and find all the managers of a worker or all the sub ordinates of a manager etc.

              The CTE has also the ability to take a degenerated select from Dual and create from it a set of all the numbers up to ...

              My question is if there is a way to use the Connect By to create a set from Dual, or it is impossible, and it works only with hierarchical tables?

               

              It is obvious that the CTE can do whatever Connect By can,

              but is it also true to the other side?

               

              Another question- in which year was released the first Oracle version which supports CTE?

               

              Thank you very much!

              • 4. Re: Can I use the Connect By operator to generate a set?
                Frank Kulash

                Hi,

                 

                 

                 

                user576036 wrote:

                 

                Sorry, I should have made myself more clear..

                 

                The Connect By is using with hierarchical tables: we take such a table, and find all the managers of a worker or all the sub ordinates of a manager etc.

                The CTE has also the ability to take a degenerated select from Dual and create from it a set of all the numbers up to ...

                My question is if there is a way to use the Connect By to create a set from Dual, or it is impossible, and it works only with hierarchical tables?

                 

                It is obvious that the CTE can do whatever Connect By can,

                but is it also true to the other side?

                 

                Another question- in which year was released the first Oracle version which supports CTE?

                 

                Thank you very much!

                CONNECT BY was designed for adjacency-model trees, like you described.  In my earlier message, I said that that was one area where CONNECT BY might be better than a recursive WITH clause.  If I gave the impression that CONNECT BY isn't good at anything else, I apologize.  It's very useful for other things, and better than recursive WITH clauses for many of them.


                For example, CONNECT BY can be used to generate a "counter" table, for example, to get 10 rows, contain the integers 1 through 10:

                 

                SELECT  LEVEL  AS n

                FROM    dual

                CONNECT BY  LEVEL  <= 10;

                This is actally a common use of CONNECT BY, and it's very efficient.

                 

                I don't think that CONNECT BY can do anything that a recursive WITH clause can do.  Maybe CONNECT BY together with MODEL, or some XML functions, can do anything that a recursive WITH clause can do, but it might be much more difficult and much less efficient.

                 

                WITH clauses were new in Oracle 9.1, released in 2001.

                Recursive WITH clauses were new in Oracle 11.2, released in 2009.

                I find it interesteing that CONNECT BY was built into Oracle 2, released in 1979.