• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1818
  • Last Modified:

ORACLE HIERARCHY TREE QUERY

Greetings,

I am having problems with two hieracrhy queries against an Oracle database. My problem is that I'm trying to display the id the field for the tree level values listed below. The code and what I currently have is listed below.

The Code:
select root,

        max(decode(cat_level,1,description)) level1,

        max(decode(cat_level,2,description)) level2,

        max(decode(cat_level,3,description)) level3

       from (

   select description, root,

          row_number() over (partition by root order by description) cat_level

     from (

   select description, connect_by_root description root

    from ticket_category_tmp
  --- start with cat_level='0'

    connect by prior id = parent_cat_code
          )

    where description <> root
         
          )
group by root

The results:
ROOT                       LEVEL1                       LEVEL2            LEVEL3
Administration      Authorizations      Changeout            Password Change



What I would like to appear in this hierarchy query:

ROOT               ID          LEVEL1                 ID      LEVEL2            ID        LEVEL3                       ID
Administration      7            Authorizations       115      Changeout            116       Password Change        117

SECOND APPROACH FOR THE SAME QUERY:

select id, sys_connect_by_path(description,' > ')tree,cat_level
from ticket_category_tmp
start with parent_cat_code=0
connect by prior id=parent_cat_code
Order By tree

RESULTS OF SECOND QUERY:
ID      TREE                                                        CAT LEVEL
7       > Administration                                            0
115       > Administration > Authorizations            1
116       > Administration > Authorizations > Changeout      2
117       > Administration > Authorizations > Trip            2
114       > Administration > Password Change            1
113       > Administration > UserID Change            1

WHAT I WOULD LIKE TO APPEAR IN THIS QUERY:
TREE                                                        CAT LEVEL
 Administration 7                                            0
 Administration 7 Authorizations 115            1
 Administration 7 Authorizations 115 Changeout 116      2
 Administration 7 Authorizations 116 Trip 117      2
0
Omega002
Asked:
Omega002
  • 3
1 Solution
 
actonwangCommented:
for second one, try:

select id, sys_connect_by_path(desc,'  ')tree,cat_level
from
(select t.*, description ||' '||id desc
from ticket_category_tmp t
) a
start with parent_cat_code=0
connect by prior id=parent_cat_code
Order By tree
/
0
 
actonwangCommented:
or:

select sys_connect_by_path(desc,'  ')tree,cat_level
from
(select t.*, description ||' '||id desc
from ticket_category_tmp t
) a
start with parent_cat_code=0
connect by prior id=parent_cat_code
Order By tree
/
0
 
actonwangCommented:
for first one,try:

///////////////////////////////////
select root,
        max(decode(cat_level,1,id)) id1,  -- add id column
        max(decode(cat_level,1,description)) level1,
        max(decode(cat_level,2,id)) id2, -- add id column
        max(decode(cat_level,2,description)) level2,
        max(decode(cat_level,3,id)) id3, -- add id column
        max(decode(cat_level,3,description)) level3

       from (

   select description, root,id,  -- add id columen

          row_number() over (partition by root order by description) cat_level

     from (

   select description, connect_by_root description root

    from ticket_category_tmp
  --- start with cat_level='0'

    connect by prior id = parent_cat_code
          )

    where description <> root
         
          )
group by root
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now