• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1834
  • 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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