asked on

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

Pawan Kumar

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 !!

Helena Marková

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

ASKER CERTIFIED SOLUTION

Geert G

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sam2929

ASKER

pawan your code don't work

Pawan Kumar

@Author - Can you post the error you r facing ?