How to add in Hierarchial or in tree?

Edisson Gabriel López

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.

Frank Kulash

Hi, @Edisson Gabriel López

Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements for all tables involved, relevant columns only) so the people who want to help you can re-create the problem and test their ideas. Also post the exact results you want from that data, and explain why you want those results from that data. Always post your complete Oracle version (e.g. 18.4.0.0.0).

Here's an example using tables from the scott schema.

WITH  connect_by_results  AS
(
	SELECT  ename, deptno, sal
	,	LEVEL   AS lvl
	,	ROWNUM  AS seq
	FROM	scott.emp
	START WITH ename = 'KING'
	CONNECT BY mgr	  = PRIOR empno
)
SELECT  m.lvl, m.ename, m.sal, m.desc_sal
,	 d.deptno, d.dname
FROM	 connect_by_results
MATCH_RECOGNIZE
	 (
	   ORDER BY       seq
	   MEASURES	  a.ename	AS ename
	   ,		  a.deptno	AS deptno
	   ,		  a.sal  	AS sal
	   ,		  a.lvl	        AS lvl
	   ,		  a.seq	        AS seq
	   ,		  SUM (sal)	AS desc_sal
	   ONE ROW PER MATCH
	   AFTER MATCH SKIP TO NEXT ROW
	   PATTERN    ( a d* )
	   DEFINE  	  d 	 AS lvl > a.lvl
	 )      m
JOIN	 scott.dept d ON d.deptno = m.deptno
ORDER BY m.seq
;

Results:

 LVL ENAME      SAL    DESC_SAL DEPTNO DNAME
---- ---------- ------ -------- ------ --------------
   1 KING         5000    29025     10 ACCOUNTING
   2 JONES        2975    10875     20 RESEARCH
   3 SCOTT        3000     4100     20 RESEARCH
   4 ADAMS        1100     1100     20 RESEARCH
   3 FORD         3000     3800     20 RESEARCH
   4 SMITH         800      800     20 RESEARCH
   2 BLAKE        2850     9400     30 SALES
   3 ALLEN        1600     1600     30 SALES
   3 WARD         1250     1250     30 SALES
   3 MARTIN       1250     1250     30 SALES
   3 TURNER       1500     1500     30 SALES
   3 JAMES         950      950     30 SALES
   2 CLARK        2450     3750     10 ACCOUNTING
   3 MILLER       1300     1300     10 ACCOUNTING

Desc_sal is the sum of the row's sal plus all the descendants' sals (if any).

Frank Kulash

Hi, @Edisson Gabriel López

Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements for all tables involved, relevant columns only) so the people who want to help you can re-create the problem and test their ideas. Also post the exact results you want from that data, and explain why you want those results from that data. Always post your complete Oracle version (e.g. 18.4.0.0.0).

Here's an example using tables from the scott schema.

WITH  connect_by_results  AS
(
	SELECT  ename, deptno, sal
	,	LEVEL   AS lvl
	,	ROWNUM  AS seq
	FROM	scott.emp
	START WITH ename = 'KING'
	CONNECT BY mgr	  = PRIOR empno
)
SELECT  m.lvl, m.ename, m.sal, m.desc_sal
,	 d.deptno, d.dname
FROM	 connect_by_results
MATCH_RECOGNIZE
	 (
	   ORDER BY       seq
	   MEASURES	  a.ename	AS ename
	   ,		  a.deptno	AS deptno
	   ,		  a.sal  	AS sal
	   ,		  a.lvl	        AS lvl
	   ,		  a.seq	        AS seq
	   ,		  SUM (sal)	AS desc_sal
	   ONE ROW PER MATCH
	   AFTER MATCH SKIP TO NEXT ROW
	   PATTERN    ( a d* )
	   DEFINE  	  d 	 AS lvl > a.lvl
	 )      m
JOIN	 scott.dept d ON d.deptno = m.deptno
ORDER BY m.seq
;

Results:

 LVL ENAME      SAL    DESC_SAL DEPTNO DNAME
---- ---------- ------ -------- ------ --------------
   1 KING         5000    29025     10 ACCOUNTING
   2 JONES        2975    10875     20 RESEARCH
   3 SCOTT        3000     4100     20 RESEARCH
   4 ADAMS        1100     1100     20 RESEARCH
   3 FORD         3000     3800     20 RESEARCH
   4 SMITH         800      800     20 RESEARCH
   2 BLAKE        2850     9400     30 SALES
   3 ALLEN        1600     1600     30 SALES
   3 WARD         1250     1250     30 SALES
   3 MARTIN       1250     1250     30 SALES
   3 TURNER       1500     1500     30 SALES
   3 JAMES         950      950     30 SALES
   2 CLARK        2450     3750     10 ACCOUNTING
   3 MILLER       1300     1300     10 ACCOUNTING

Desc_sal is the sum of the row's sal plus all the descendants' sals (if any).

Edisson Gabriel López

https://community.oracle.com/tech/developers/discussion/comment/16837462#Comment_16837462

Hi,

Sorry, Oracle 19 Release v19.0.0.0.0.

What is a JOIN to the dept table for? What is the functionality of ONE ROW PER MATCH AFTER MATCH SKIP TO NEXT ROW?

(	   
	ONE ROW PER MATCH
	AFTER MATCH SKIP TO NEXT ROW
	PATTERN    ( a d* )
	DEFINE  	  d 	 AS lvl > a.lvl
)
JOIN	 scott.dept d ON d.deptno = m.deptn

I tried to comment them, but I got an error. I mean, I already have the value and I don't have to relate more tables, I just need to show the total for each level, but I'm confused by the dept table.

ORA-00905:

SELECT  m.LVL, m.PK1_START_VALUE, m.TOTAL, m.desc_sal, m.PARENT_PK1_VALUE
FROM	 connect_by_results
MATCH_RECOGNIZE
	 (
	   ORDER BY  seq
	   MEASURES	  a.PK1_START_VALUE	AS PK1_START_VALUE
	   ,		  a.PARENT_PK1_VALUE	AS PARENT_PK1_VALUE
	   ,		  a.TOTAL  	AS TOTAL
	   ,		  a.lvl	        AS lvl
	   ,		  a.seq	        AS seq
	   ,		  SUM (TOTAL)	AS desc_sal
	 )      m
ORDER BY m.seq

I am going to try to build the Oracle Fusion tables in SQL Live.

The official documentation for the table is this https://docs.oracle.com/en/cloud/saas/applications-common/21d/oedma/fndtreenode-22816.html#fndtreenode-22816

Thank you.

Frank Kulash

Hi @Edisson Gabriel López

What is a JOIN to the dept table for?

To get the department name.

What is the functionality of ONE ROW PER MATCH AFTER MATCH SKIP TO NEXT ROW?

ONE ROW PER MATCH means there will be only one row in the result set for every match found, regardless of how many rows were in the pattern.

MATCH_RECOGNIZE is looking for the pattern a d* , that is, an ancestor followed by any number (0 or more) descendants. After finding a match, MATCH_RECOGNIZE will look for a new match. By default, it will start looking at the first row after the end of the last matching pattern, that is, after the last descendant. In this case, we want to start looking for another pattern on the first row after the old pattern began, which is what AFTER MATCH SKIP TO NEXT ROW does.

I tried to comment them, but I got an error. 

It looks like you removed the PATTERN and DEFINE clauses, too. MATCH_RECOGNIZE needs both.

 I don't have to relate more tables,

In the query you posted, you did join more tables. The CONNECT BY was done on fnd_tree_node, then you joined the results to fnd_vs_value_vl, gl_je_lines and gl_code_combinations.

I just need to show the total for each level,

In the query you posted, you limited the CONNECT BY search to 5 levels, but then you said "Example of the result I need" and showed 21 rows. Which is it: 5 or 21?

Once again, post sample data for your tables, or post an example lf what you're trying to do using the scott tables.

Edisson Gabriel López

https://community.oracle.com/tech/developers/discussion/comment/16837487#Comment_16837487

Hi Frank Kulash,

It worked for me, thank you.

Just an inquiry, If I did not use a JOIN, how would this part of the query look like? 

(	   
	ONE ROW PER MATCH
	AFTER MATCH SKIP TO NEXT ROW
	PATTERN    ( a d* )
	DEFINE  	  d 	 AS lvl > a.lvl
)

Thank you.

Frank Kulash

Hi, @Edisson Gabriel López

Just an inquiry, If I did not use a JOIN, how would this part of the query look like?

It would look exactly the same. MATCH_RECOGONIZE is being done first, then dept is joined to m, the result set of MATCH_RECOGNIZE.