SQL & PL/SQL

Hi everyone

I'm seeing an ORA-01436 error - CONNECT BY loop in user data - when I'm running the query below:

select distinct(pose.organization_id_child)
from per_org_structure_elements pose
where pose.org_structure_version_id =
(select posv.org_structure_version_id
from per_org_structure_versions posv
where trunc(sysdate) between posv.date_from and nvl(posv.date_to,'31-DEC-4712'))
start with pose.organization_id_parent = xxx
connect by prior pose.organization_id_child = pose.organization_id_parent;

(I'm connecting to a Oracle9i Enterprise Edition Release 9.2.0.8.0 database by the way)

This error doesn't always crop up - if I change the organization_id_parent value in the start with section of the query above, I find that it only happens for certain organization_ids. However, for the organization_ids where it does happen, upon examining the data for both parent and child rows, I can't understand why a CONNECT BY loop might be happening.

For example, the two rows below cause issues whenever I try to use their organization_id_parent in the query:

Senior Store Manager level (second from bottom):

ORG_STRUCTURE_ELEMENT_ID 34305
BUSINESS_GROUP_ID 1
ORGANIZATION_ID_PARENT 667
ORG_STRUCTURE_VERSION_ID 224
ORGANIZATION_ID_CHILD 571

Store level (bottom level):

ORG_STRUCTURE_ELEMENT_ID 34304
BUSINESS_GROUP_ID 1
ORGANIZATION_ID_PARENT 571
ORG_STRUCTURE_VERSION_ID 224
ORGANIZATION_ID_CHILD 570

As you can see, the org_id_parent value at the bottom level is greater than the actual org_id_child value at that same level. Could this be what is causing the ORA-01436 error? It's the only thing that's come to mind so far!

Any help would be gratefully received.

Thanks, Geoff