Oracle GL tree hierarchy

Question

This is the code:

SELECT
  'FXE_I_823' AS KEY,
  listagg(ftn.pk1_start_value || '|' || ffvv.description, '|')
    within GROUP (ORDER BY DEPTH) "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 = 'ARA40'
AND ffvv.value_category = 'COST CENTER'

I am getting the below output:

ARA40|ARA40|REG059|Reg 59 - Ops-Transport North|DST0418|Dist 418 Trans OpsPhiladelphia|CLU5110|Cluster 5110|SPK5110|Spoke Centers 5110|1623501|1623501 - LOMG Retail Location|1623507|1623507 - Retail Freight Service ACIM

But I am expecting the output like this:

ARA40|REG059|Reg 59 - Ops-Transport North|DST0418|Dist 418 Trans OpsPhiladelphia|CLU5110|Cluster 5110|SPK5110|Spoke Centers 5110|1623501|1623501 - LOMG Retail Location
ARA40|REG059|Reg 59 - Ops-Transport North|DST0418|Dist 418 Trans OpsPhiladelphia|CLU5110|Cluster 5110|SPK5110|Spoke Centers 5110|1623507|1623507 - Retail Freight Service ACIM

The last node should not come in first line and come in second line like recusrion and also the parent node ARA40 should not fetch description only child nodes from second value should have description. Can someone please help

"...The last node should not come in first line and come in second line..." -- Use the `ORDER BY` clause to specify how you want the result to be sorted. — The Impaler, Oct 31 '22 at 13:48
Please add a few rows of sample data and the expected result. — The Impaler, Oct 31 '22 at 13:49

score 0 · Answer 1 · answered Nov 09 '22 at 00:11

The data in the fnd_tree_node table should have a parent/child relationship between values. The goal appears to be:

begin at the first child node in the tree
find each leaf node under that hierarchy level
build the path between node level 1 and node level n - including delimiters and descriptions
return each result on a separate row

This can be accomplished using a hierichical query starting with each value at the second-lowest depth and connecting by its parent values. The tree can be flattened using SYS_CONNECT_BY_PATH. The desired rows (leaves) may be filtered using the CONNECT_BY_ISLEAF pseudocolumn.

Note on SYS_CONNECT_BY_PATH: If the separator character (second parameter) appears anywhere within the data for the first parameter, one will receive an ORA-30004 error. That is why a placeholder is used in the example below.

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 = 'ARA40'
   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

Oracle GL tree hierarchy

1 Answers1