Hierarchical Query/Clause Problem
Daniel Karwatka's CONNECT BY NOCYCLE query on a 289-row table looped forever. Doug Lawry pointed out PRIOR must go before the parent column (PRIOR sub_item_code = item_code), which fixed it. On a larger data set the query again looped at levels 32-38. Doug suspected multi-level cycles, suggested limiting LEVEL in CONNECT BY or opening a support case. Partly unresolved.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
|
view attached
I'm having trouble with a hierarchical query. I have a table of 289 records on which I'm running the following query and it ends up being an infinite loop. Does anyone have an idea about what I might be doing wrong?
create temp table tmp_sub(item_code char(24), sub_item_code char(24));I've attached the sample data file.
SELECT item_code, sub_item_code, LEVEL level,CONNECT_BY_ISLEAF leaf, CONNECT_BY_ISCYCLE cycle, CONNECT_BY_ROOT item_code rt
FROM tmp_sub
START WITH item_code = "JVC2SA1037K(QR)-W"
CONNECT BY NOCYCLE PRIOR item_code = sub_item_code;
Thanks.
------------------------------
Daniel Karwatka
------------------------------
Hi Daniel. Sorry: I only just saw this which is interesting. The order of the CONNECT BY clause matters. You must have PRIOR in front of the higher level column, whereas you had it friont of the lower level column: https://www.ibm.com/docs/en/informix-servers/14.10.0?topic=clause-conditions-in-connect-by It doesn't run forever the other way round: SELECT item_code, sub_item_code, LEVEL level, CONNECT_BY_ISLEAF leaf, CONNECT_BY_ISCYCLE cycle, CONNECT_BY_ROOT item_code rt FROM tmp_sub START WITH item_code = "JVC2SA1037K(QR)-W" CONNECT BY NOCYCLE PRIOR sub_item_code = item_code; Results are then: ------------------------------ Doug Lawry Oninit Consulting ------------------------------
| view attached Thank you so much for the clarification. I missed that PRIOR is a unary operator only for the column name that immediately follows it. Unfortunately, when I expand the query to a larger subset of my data it starts looping at levels 32-38. I've uploaded another sample data file that has a different subset of data which seems to cause the query to go into an infinite loop. ------------------------------ Daniel Karwatka ------------------------------
Hi Daniel. I don't have time to try that today, but most likely you have data that loops back multiple levels. Maybe NOCYCLE in Informix only tracks a limited number of levels, and you need to open a support case with IBM. I have been testing with IDS 14.10.14. Meanwhile, you could add "WHERE LEVEL <= sane-number " to the CONNECT BY clause. ------------------------------ Doug Lawry Oninit Consulting ------------------------------