M1k3G wrote:Thanks for posting the CREATE TABLE and INSERT statments; that's very helpful.
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?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 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.
SELECT reqitemid FROM requisition_items ;
seems to be sufficant.
select reqitemid from REQUISITION_ITEMS where reqid in (select reqid from REQUISITIONS)
M1k3G wrote: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:
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.
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:
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>
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>
M1k3G wrote: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.
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,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.
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.)