Recuperating tree structure in SQL without content duplication

Question

I have a tree structure that I am trying to recuperate via a statement in sql developer for an output display. The tree is of four levels, while level four represents the documents attached on level three.

this is my query:

select CASE WHEN level=4 THEN node.name  END as Level_One,
       CASE WHEN level=5 THEN node.name  END as Level_Two,
       CASE WHEN level=6 THEN node.name  END as Level_Three,
       doc.lib_doc as LABEL

  from treenode node

  left join rules ru on ru.datatype = node.octype

  left join prs pr on pr.key = ru.datatype

  left join file fl on fl.code = pr.prcode

  left join ref_doc refd on refd.mn_doc = fl.code
 
  left join ref_document doc on refd.id_doc = doc.id_doc

  connect by prior node.key = node.father_id

  start with node.father_id =-1;

The problem is that I am displaying the same level three node.name for each doc.lib_doc attached and thus getting the same value duplicated as much as there are doc.lib_doc as below:

Level_One	Level_Two	Level_Three	LABEL
1 - resp	1 - Right	1 - Element	abc
		1 - Element	test
		1 - Element	125
		1 - Element	test1
		1 - Element	file
		2 - ElementA	fileXYZ
		2 - ElementA	fileABC
2 - respa	1 - Access	1 - right	abc

While I am without success trying to get the display below without duplication in tree levels:

Level_One	Level_Two	Level_Three	LABEL
1 - resp	1 - Right	1 - Element	abc
			test
			125
			test1
			file
		2 - ElementA	fileXYZ
			fileABC
2 - respa	1 - Access	1 - right	abc

Any suggestions on how to achieve this behavior in the statement?

Raseena Abdul · Answer 1 · 2021-03-05T20:44:02.800

0

You could use lag to see the prev value and compare. I know the below syntax for lag works in SQL Server. Not sure about oracle though.

select CASE WHEN level=4 THEN node.name  END as Level_One,
       CASE WHEN level=5 THEN node.name  END as Level_Two,
   CASE WHEN level=6 THEN  decode ( lag(node.name, 1) over( order by null), node.name, '', node_name) END as Level_Three,
        doc.lib_doc as LABEL

  from treenode node

  left join rules ru on ru.datatype = node.octype

  left join prs pr on pr.key = ru.datatype

  left join file fl on fl.code = pr.prcode

  left join ref_doc refd on refd.mn_doc = fl.code
 
  left join ref_document doc on refd.id_doc = doc.id_doc

  connect by prior node.key = node.father_id

  start with node.father_id =-1;

edited Mar 05 '21 at 20:44

answered Mar 05 '21 at 17:21

Raseena Abdul

516
3
6

I am getting missing from on select 1... what are you trying to do with the select 1 ? – mikeb Mar 05 '21 at 17:28
order by is a required field.. and i don't see any ordering for your data. The select 1 is to circumvent that. Similar to :- https://stackoverflow.com/questions/44105691/row-number-without-order-by – Raseena Abdul Mar 05 '21 at 17:31

Recuperating tree structure in SQL without content duplication

1 Answers1