Hi,
I need to sum in a tree by levels, but I can't find much information on how to do it, I have the following query:
SELECT
TREE.*,
FVVVL.DESCRIPTION,
--SUM(VALUES_GL.TOTAL)
VALUES_GL.TOTAL
FROM (
SELECT
PK1_START_VALUE,
PARENT_PK1_VALUE,
CONNECT_BY_ISCYCLE "Cycle",
LEVEL,
SYS_CONNECT_BY_PATH(PK1_START_VALUE, '/') "Path"
FROM
FND_TREE_NODE
WHERE TREE_CODE = 'CGM_ESF'
START WITH PK1_START_VALUE = 'ESF_A'
CONNECT BY NOCYCLE PRIOR PK1_START_VALUE = PARENT_PK1_VALUE AND LEVEL <= 5
--ORDER SIBLINGS BY PK1_START_VALUE
) TREE
INNER JOIN FND_VS_VALUES_VL FVVVL ON FVVVL.VALUE = TREE.PK1_START_VALUE
LEFT JOIN (
SELECT
NVL(GLL.ACCOUNTED_DR, GLL.ACCOUNTED_CR * -1 ) AS TOTAL,
GLL.PERIOD_NAME, TO_CHAR(GLL.EFFECTIVE_DATE, 'DD-MM-YYYY'),
GLL.CODE_COMBINATION_ID,
GLL.LEDGER_ID,
GCC.SEGMENT2
FROM GL_JE_LINES GLL
INNER JOIN GL_CODE_COMBINATIONS GCC ON GLL.CODE_COMBINATION_ID = GLL.CODE_COMBINATION_ID
) VALUES_GL ON VALUES_GL.SEGMENT2 = TREE.PK1_START_VALUE --AND TREE.LEVEL = 5
ORDER BY "Path"
Example of the result I need:
Output of the result without sum:
But when I want to add I get the following error, ORA-01788:
Thank you.