Hello

I have the following SQL which is working fine, however I need the information at the lowest level . This SQL only provide the first child values but I have more child values.

Although I change the value to zero I still see only the first child on the hierarchy.

i.e. START WITH depth = 0

Thanks

SELECT    'FXE_I_823' key

         ,ftn.tree_code

          || REPLACE(SYS_CONNECT_BY_PATH(ftn.pk1_start_value

                                         || CHR(0)

                                         || ffvv.description

                                         ,'|')

                    ,CHR(0)

                    ,'|') tree_code

 FROM    fnd_tree_node     ftn

         ,fnd_flex_values_vl ffvv

 WHERE    1 = 1

  AND    ftn.pk1_start_value = ffvv.flex_value

  AND    ftn.tree_code = 'COA_ACCOUNT'

  AND    ffvv.value_category = 'COST CENTER'

  AND    CONNECT_BY_ISLEAF = 1

CONNECT BY PRIOR ftn.pk1_start_value = ftn.parent_pk1_value

START WITH depth = 1 -- root node is at depth zero