Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# levels for reporting

Posted on 2016-09-26
Medium Priority
106 Views
Hi,
Below query gives below results:

select

*

from

(

Select

a.position_nbr,

a.REPORTS_TO,

a.POSITION_desc,

a.Position_level

ROW_NUMBER() OVER (PARTITION BY a.position_nbr ORDER BY a.EFFDT DESC) AS RN

from PS_POSITION_DATA a
)

where RN='1'

position_nbr reports_to   POSITION_desc Position_level

143               12                          CEO                                  CEO

038              143                        DIRECTOR                       DIR

114              143                       DG                                   DG

346              114                     Manager                          MAN

i want results like below so idea is start from level1 where position_level ='CEO ' and show who reports to CEO and then who reports to DG etc. Max level is 8

level1  level2 level3 level4 level5 level6 level7 level8  position_desc position_level

143                                                                                              CEO            CEO

038                                                                                  DIRECTOR        DIR

114                                                                              DG              DG

346                                                                  Manager         MAN

0
Question by:sam2929
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 32

Expert Comment

ID: 41817063
Here is the complete code

``````;WITH CTE
AS
(

Select position_nbr , reports_to , POSITION_desc , Position_level, 0 Level
From PS_POSITION_DATA  E Where E.REPORTS_TO = 'CEO'

UNION ALL

Select E.position_nbr, E.reports_to, c.Level + 1 ,  E.POSITION_desc , E.Position_level
From PS_POSITION_DATA  E INNER JOIN CTE c on c.position_nbr = e.REPORTS_TO

)
SELECT * FROM CTE H

``````

Enjoy !!
0

LVL 22

Expert Comment

ID: 41817277
Mybe you can try hierarchical query:
SELECT LPAD(' ',2*(LEVEL-1)) || to_char(position_nbr) lvl ,
POSITION_desc, Position_level
FROM PS_POSITION_DATA
CONNECT BY PRIOR position_nbr = reports_to NOCYCLE;

http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_10002.htm#SQLRF01702
0

LVL 38

Accepted Solution

Geert Gruwez earned 2000 total points
ID: 41817683
you can use sys_connect_by_path to display the hierarchy

``````select  position_nbr, REPORTS_TO, POSITION_desc,
level position_level,
sys_connect_by_path(position_desc, ' / ') reporting_chain
from PS_POSITION_DATA
connect by prior position_nbr = reports_to
``````
0

Author Comment

ID: 41837728
0

LVL 32

Expert Comment

ID: 41857957
@Author - Can you post the error you r facing ?
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
###### Suggested Courses
Course of the Month11 days, left to enroll