11 Replies Latest reply: Feb 5, 2012 2:22 PM by M1k3G RSS

    Connect by no cycle

    M1k3G
      The connect_by clause I am using returns far more data than there are lines. The data is gathered from 2 other tables, which are named Requisitions and Requisition_Items. I provide some sample tables and sample data below. The question at this point is if it possible to use CONNECT BY over 2 tables, as in my example?

      Being unfamiliar with this clause, I am applying simple SELECT logic. If I run a simple select with the same data, I get the correct number of lines - 3. When I try CONNECT BY PRIOR, I get the message "Connect By Loop In User Data. When I try the Connect BY NOCYCCLE, I get an endless loop. Is there a way around this?

      {code}

      create table REQUISITIONS
      (
      reqid VARCHAR2(38)
      );

      INSERT INTO REQUISITIONS VALUES
      ('682CCD75-2546-4A5B-A6D8-052F61D468F5');

      create table REQUISITION_ITEMS
      (
      reqid VARCHAR2(38),
      reqitemid VARCHAR2(38)
      );
      INSERT INTO REQUISITION_ITEMS
      VALUES
      ('682CCD75-2546-4A5B-A6D8-052F61D468F5',
      'AFA25B8B-CD2B-45CB-8A56-6682022C7BC3');
      INSERT INTO REQUISITION_ITEMS
      VALUES
      ('682CCD75-2546-4A5B-A6D8-052F61D468F5',
      '0CBB858B-4485-4A24-BF9B-96F57D35AC28');
      INSERT INTO REQUISITION_ITEMS
      VALUES
      ('682CCD75-2546-4A5B-A6D8-052F61D468F5',
      '9A0A725D-7BD7-4A1C-8688-1A5501EBC88F');


      SELECT t.reqitemid
      FROM REQUISITION_ITEMS t, REQUISITIONS s
      WHERE t.reqid = s.reqid
      CONNECT BY NOCYCLE t.reqid = s.reqid;

      {code}
        • 1. Re: Connect by no cycle
          Frank Kulash
          Hi,
          M1k3G wrote:
          The connect_by clause I am using returns far more data than there are lines. The data is gathered from 2 other tables, which are named Requisitions and Requisition_Items. I provide some sample tables and sample data below.
          Thanks for posting the CREATE TABLE and INSERT statments; that's very helpful.
          Don't forget to post the results you want from that data. Explain how you get those results from that data.
          Always way which version of Oracle you're using. This is especially important with CONNECT BY queries, because every version since Oracle 7 has had significant changes in this area. Don't just include an easy-to-miss tag like "11g" (as if there were an 11f or 11h); come right out and say something like "I'm using Oracle 11.2.0.1.0".
          The question at this point is if it possible to use CONNECT BY over 2 tables, as in my example?
          Yes, it's possible to do joins in a CONNECT BY query, but it's usually much more efficient to do the joins in a separate query, either before or after the CONNECT BY.
          Being unfamiliar with this clause, I am applying simple SELECT logic. If I run a simple select with the same data, I get the correct number of lines - 3. When I try CONNECT BY PRIOR, I get the message "Connect By Loop In User Data. When I try the Connect BY NOCYCCLE, I get an endless loop. Is there a way around this?
          It's unclear that you need a CONNECT BY query at all. I'll be able to say more about this after I see your desired results, and the explanation of how you get those resutls from that data.

          I a nutshell, here's how CONNECT BY works.
          The START WITH clause determines which rows are on LEVEL=1. You can omit the START WITH clause, in which case all rows are on LEVEL=1.
          When deciding which rows go on LEVEL=N (N>1), the query looks at each row in the table, and, if the CONNECT BY clause is true, that row joins the result set on LEVEL=N. The same row from the table can be in the result set more than once.
          If the CONNECT BY clause is *ever* TRUE for a given row, then it will *always* be true for that row, unless it contains
          (a) a reference to some row on LEVEL=N-1, using the PRIOR operator
          (b) a condition such as LEVEL < x, or
          (c) some random element (not very practical).
          The CONNECT BY clause should always include at least one of the above.
          • 2. Re: Connect by no cycle
            M1k3G
            The results I would like to return are the Requisition Item. In this case:

            'AFA25B8B-CD2B-45CB-8A56-6682022C7BC3'
            '0CBB858B-4485-4A24-BF9B-96F57D35AC28'
            '9A0A725D-7BD7-4A1C-8688-1A5501EBC88F'

            I can't use connect by level to achieve this, as I can never know how many lines I need to retrieve. This time its 3 lines, but the next requisition may be 10, 20 or 100 lines. I had tried connect by prior, but as I mentioned, I get an error message. Perhaps you know how I can rewrite Connect BY Prior to make it work?
            • 3. Re: Connect by no cycle
              Frank Kulash
              Hi,

              Here's one way to get those results:
              SELECT     reqitemid
              FROM     requisition_items
              ;
              I suspect that this just happens to get the right results from your sample data. That's why you need to post an explanation of how you get those results from that data.
              Is that sample data really enough to show what your problem is? For example, you talk about having varying numbers of levels. It looks like all the rows in your sample data are on the same level.
              Do you only have one row in your real requistions table, and only one distinct value in your real requisition_items table? Are you really showing what you need to do by only having one value in the sample data?
              Is there some kind of parent-child relationship in this data? Explain it.

              No doubt this is all second nature to you, but I'm not as familiar with your tables and your application as you are, so I need help understanding what you're trying to do.
              • 4. Re: Connect by no cycle
                Etbin
                Looking at your data it seems a classic master-detail relationship.

                Regards

                Etbin
                • 5. Re: Connect by no cycle
                  chris227
                  Perhaps you missed something on the connect by construct. It works on chains of values, think of linked lists.
                  In your case it seems to me, that you only want to get the requestiton items for a requisition.

                  so
                  select reqitemid from REQUISITION_ITEMS where
                  reqid in
                  (select reqid from
                  REQUISITIONS)
                  seems to be sufficant.

                  Regards chris
                  • 6. Re: Connect by no cycle
                    M1k3G
                    Hello Frank. My example is simplified, as you suspected. However, the ReqID is the only link between the 2 tables. It is the Primary key in the table Requisitions, and the Foreign key in the Requisition_Items. Maybe my understanding of what a Parent-Child relationship is flawed, but I thought that anything in the table Requisition_Items is in a Child relationship to entries in the table Requisitions. I'm really only trying to understand how CONNECT BY PRIOR works, as I would like to use it to generate a sequence in a temp table. I can't use a proper sequence, as it needs to start from 1 whenever a new Requisition is captured. The number of lines of Requisition_Items is unpredictable, so I can't use CONNECT BY LEVEL < ??. I also can't use a Procedure to reset the Sequence, because a Scheduler would have to know beforehand when the next requisition is being inserted to reset it.

                    If the Requisition table is considered the Parent, and Requisition_Items is the Child, is there some way of union Connect By Prior so that there is no looping error.

                    Thanks very much for your time.
                    • 7. Re: Connect by no cycle
                      M1k3G
                      Hello Chris. There is no problem in finding a SELECT to extract the data. The example is simplified to understand how CONNECT BY works.
                      • 8. Re: Connect by no cycle
                        Solomon Yakobson
                        M1k3G wrote:

                        If the Requisition table is considered the Parent, and Requisition_Items is the Child, is there some way of union Connect By Prior so that there is no looping error.
                        Yes, you can look at FK relationship that way. But it is one level hierarchy and therefore is no different from plain join. So when you use CONNECT BY t.reqid = s.reqid (with or without NOCYCLE) you define endless hierrarchy - row is child of itself. Now why NOCYCLE does not help? Because your CONNECT BY condition does not use PRIOR. Only PRIOR forces Oracle to consider hierarchy loops. Now what do you need to make it work? Obviously you need to add PRIOR. And the hierarchy if this case is one parent - one or more children, right? So this is your PRIOR condition:
                        SELECT  t.reqitemid
                          FROM  REQUISITION_ITEMS t,
                                REQUISITIONS s
                          WHERE t.reqid = s.reqid
                          CONNECT BY NOCYCLE t.reqid = s.reqid
                                 AND s.reqid = PRIOR s.reqid
                        /
                        
                        REQITEMID
                        --------------------------------------
                        AFA25B8B-CD2B-45CB-8A56-6682022C7BC3
                        0CBB858B-4485-4A24-BF9B-96F57D35AC28
                        9A0A725D-7BD7-4A1C-8688-1A5501EBC88F
                        
                        SQL> 
                        But, as I already noted, this is nothing but plain join and even worse - CONNECT BY is probably most inefficent way of joining FK relationship. Just use plain join:
                        SQL> SELECT  t.reqitemid
                          2    FROM  REQUISITION_ITEMS t,
                          3          REQUISITIONS s
                          4    WHERE t.reqid = s.reqid
                          5  /
                        
                        REQITEMID
                        --------------------------------------
                        AFA25B8B-CD2B-45CB-8A56-6682022C7BC3
                        0CBB858B-4485-4A24-BF9B-96F57D35AC28
                        9A0A725D-7BD7-4A1C-8688-1A5501EBC88F
                        
                        SQL> 
                        SY.
                        • 9. Re: Connect by no cycle
                          Etbin
                          As others already pointed out the hierarhical approach to parent-child relationship is at least inefficient if not inappropriate.
                          If you want just to investigate how connect by works use the employee-manager examples from Scott/Tiger (where managers have managers too).
                          Is there something else bothering you that we don't know of ?
                          Perhaps: a requisition_item must belong to a single requisition ! There are (unique) constraints to be defined for handling that.

                          Regards

                          Etbin
                          • 10. Re: Connect by no cycle
                            Frank Kulash
                            Hi,
                            M1k3G wrote:
                            Hello Frank. My example is simplified, as you suspected. However, the ReqID is the only link between the 2 tables. It is the Primary key in the table Requisitions, and the Foreign key in the Requisition_Items. Maybe my understanding of what a Parent-Child relationship is flawed, but I thought that anything in the table Requisition_Items is in a Child relationship to entries in the table Requisitions.
                            Yes, that is a parent-child relationship, a special case that only has two levels: parent and child. As others have said, a join is the most efficient way to deal with that kind of relationship. You might use CONNECT BY if, in addigtion to parents and children, you somethimes had grandchildren, great-grandchildren, and so on. for example, if only some of the rows in reuistion_items had parents in requisitions, and the others had parents (and maybe grandparents, great-grandparents, and so on) in requisition items.
                            I'm really only trying to understand how CONNECT BY PRIOR works,
                            That's best done in a problem where CONNECT BY really helps. It's unclear that CONNECT BY is appropriate for this problem at all.
                            If you're serious about understanding how CONNECT BY works, see
                            http://philip.greenspun.com/sql/trees.html
                            as I would like to use it to generate a sequence in a temp table. I can't use a proper sequence, as it needs to start from 1 whenever a new Requisition is captured. The number of lines of Requisition_Items is unpredictable, so I can't use CONNECT BY LEVEL < ??. I also can't use a Procedure to reset the Sequence, because a Scheduler would have to know beforehand when the next requisition is being inserted to reset it.
                            Use the analytic ROW_NUMBER function. (In some string aggregation problems, using ROW_NUMBER is actually a pre-requisite for doing CONNECT BY.)
                            If you would post sample data and results that really reflected what you want to do, then somebody could show you how to do it.

                            Edited by: Frank Kulash on Feb 5, 2012 3:22 PM
                            • 11. Re: Connect by no cycle
                              M1k3G
                              Thanks Solomon. I see your point, there was no need for CONNECT BY in this case. The example was simplified to help me understand how it works. Your response helped me do that.