SQL & PL/SQL

Hi there, I have a query which need to query hierarchical data from our chart of account (segment3). This parent-child relationship involve having many branches and leaves. I would like to be able to pull out the longest path for each of them and not all the values. Here's the case.

Value 1121 is the lowest level and its hierarchiy look like this.
---------------------------------------------------------------------
1121
/ \
4CQU TOUS
/
3CQU
-------------
/ \ \
2DES LQLO LT02
| |
1VTI LT01

or
********************************
1121 - 4CQU - 3CQU - 2DES - 1VTI
1121 - 4CQU - 3CQU - LQLO
1121 - 4CQU - 3CQU - LT02 - LT01
1121 - TOUS


With the following query
--------------------------------------------------------------
SELECT SYS_CONNECT_BY_PATH(parent_flex_value, '.')||'.'||f1.flex_value path,level
FROM fnd_flex_value_children_v f1
WHERE f1.flex_value_set_id = 1005215
AND f1.flex_value = '1121'
CONNECT BY PRIOR flex_value = parent_flex_value;

I get the following
----------------------------
PATH LEVEL
----------------------------------------- ---------
.1VTI.2DES.3CQU.4CQU.1121 4
.2DES.3CQU.4CQU.1121 3
.3CQU.4CQU.1121 2
.4CQU.1121 1
.LQLO.3CQU.4CQU.1121 3
.LT01.LT02.3CQU.4CQU.1121 4
.LT02.3CQU.4CQU.1121 3
.TOUS.1121 1

I would like to pull out only the following
----------------------------------------------------------------
.1VTI.2DES.3CQU.4CQU.1121 4
.LQLO.3CQU.4CQU.1121 3
.LT01.LT02.3CQU.4CQU.1121 4
.TOUS.1121 1

Anyone knows how can this be done? I'm using 10g database along with 11.5.10.2 apps.

Thanks a lot.

Edited by: ChrisT1826 on 2010-07-21 06:56