Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 114
  • Last Modified:

levels for reporting

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

0418             143                       ADVISOR                        ADV

114              143                       DG                                   DG

346              114                     Manager                          MAN

202              114                    Lead                                  LEAD

 

 

 
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

          0418                                                                              ADVISOR         ADV

          114                                                                              DG              DG

                   346                                                                  Manager         MAN

                   202                                                                  Lead            LEAD
0
sam2929
Asked:
sam2929
1 Solution
 
Pawan KumarDatabase ExpertCommented:
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 
	 

Open in new window


Enjoy !!
0
 
Helena Markováprogrammer-analystCommented:
Mybe you can try hierarchical query:
SELECT LPAD(' ',2*(LEVEL-1)) || to_char(position_nbr) lvl ,
        POSITION_desc, Position_level
    FROM PS_POSITION_DATA
    START WITH Position_level = 'CEO'
    CONNECT BY PRIOR position_nbr = reports_to NOCYCLE;

http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_10002.htm#SQLRF01702
0
 
Geert GOracle dbaCommented:
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 
start with POSITION_desc = 'CEO'

Open in new window

0
 
sam2929Author Commented:
pawan your code don't work
0
 
Pawan KumarDatabase ExpertCommented:
@Author - Can you post the error you r facing ?
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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