SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Issue with CONNECT_BY_ISLEAF

565839Mar 7 2007 — edited Mar 7 2007

hello-
Please help me. CONNECT_BY_ISLEAF is returning weird values for the same hierarchal query but for different column values.

Query
----
SELECT
-- BUSINESS_ENTITY_BGI_ID Business_entity,
-- PARENT_BGI_ID parent_entity,
CONNECT_BY_ISLEAF bottom_flag,
FROM STAGING_BUSINESS_ENTITY
START WITH ltrim(rtrim(BUSINESS_ENTITY_BGI_ID)) = ltrim(rtrim(PARENT_BGI_ID))
CONNECT BY NOCYCLE PRIOR BUSINESS_ENTITY_BGI_ID = PARENT_BGI_ID

Example 1(values)
--------
Business_entity Parent_entity
--------------- ------------
BED663100063 BED865374474
BED865374474 BED865374474

Result
------
CONNECT_BY_ISLEAF
-----------------
1
0

Then i modified the values just by a single number and the results I'm getting are different

Modified values
---------------
Business_entity Parent_entity
--------------- ------------
BED663100063 BED265374474
BED265374474 BED265374474

Result
----------
CONNECT_BY_ISLEAF
-----------------
1
1

I just couldn't figured it out why its giving different results for the same query. Please help!

Thanks

Sri

Locked Post

New comments cannot be posted to this locked post.

Locked on Apr 4 2007

Added on Mar 7 2007

7 comments

481 views