Home » SQL & PL/SQL » SQL & PL/SQL » SQL help on hierarchy data (Oracle 10g and DB2)
SQL help on hierarchy data [message #666233] Sun, 22 October 2017 13:44 Go to next message
srinivas.k2005
Messages: 359
Registered: August 2006
Senior Member
Hi,

I need your help in getting flatten-hierarchical data for below without using PIVOT


CREATE TABLE hierarchy_data
(
T_EMPL_FIRST VARCHAR2(100),  T_EMPL_LAST VARCHAR2(100),  I_EMPL VARCHAR2(100),  I_EMPL_POSN_ID VARCHAR2(100), 
 I_DIR_MGR_POSN_ID VARCHAR2(100),  I_NXT_MGR_POSN_ID VARCHAR2(100), 
 I_DIR_MGR_EMPL_ID VARCHAR2(100),  I_NXT_MGR_EMPL_ID VARCHAR2(100),  EMPLOYEE_LEVEL VARCHAR2(100)
);

INSERT iNTO hierarchy_data values ('Mike','Dell','86721','50087190','','','','','0');
INSERT iNTO hierarchy_data values ('Raman','Sapra','960280','50629600','50087190','50087190','86721','8','1');
INSERT iNTO hierarchy_data values ('Aravind','I','970756','50693135','50087190','50087190','86721','8','1');
INSERT iNTO hierarchy_data values ('Raghu','Jha','964036','50626913','50693135','50693135','970756','97','2');
INSERT iNTO hierarchy_data values ('Rajesh','Moore','897022','50193758','50693135','50693135','970756','97','2');
INSERT iNTO hierarchy_data values ('Shobhit','D','965834','50659457','50626913','50626913','964036','96','3');
INSERT iNTO hierarchy_data values ('Mahesh','P','965788','50659459','50626913','50626913','964036','96','3');
INSERT iNTO hierarchy_data values ('Suresh','K','974714','50162001','50659098','50659457','','96','4');
INSERT iNTO hierarchy_data values ('Ramesh','R','9821','50182348','50659098','50659457','','96','4');
INSERT iNTO hierarchy_data values ('Thiru','S','976941','50587565','50162001','50162001','974714','97','5');
INSERT iNTO hierarchy_data values ('Srini','K','972061','50653532','50162001','50162001','974714','97','5');

commit;

I need output as below, the maximum level is 8
below copied from excel excepted results please bear with the format

FIRST1	NAME1	I_DIR_MGR_EMPL_ID 1	FIRST2	NAME2	I_DIR_MGR_EMPL_ID 2	FIRST3	NAME3	I_DIR_MGR_EMPL_ID 3	FIRST4	NAME4	I_DIR_MGR_EMPL_ID4	FIRST5	NAME5	I_DIR_MGR_EMPL_ID5	FIRST6	NAME6	I_DIR_MGR_EMPL_ID6	EMP LEVEL
Mike	Dell	NULL	Aravind	I	86721	Raghu	Jha	970756	Shobhit	D	964036	Suresh	K	NULL	Thiru	S	974714	5
Mike	Dell	NULL	Aravind	I	86721	Raghu	Jha	970756	Shobhit	D	964036	Suresh	K	NULL	Srini	K	974714	5
Mike	Dell	NULL	Aravind	I	86721	Raghu	Jha	970756	Shobhit	D	964036	Suresh	K	NULL	NULL	NULL	NULL	4
Mike	Dell	NULL	Aravind	I	86721	Raghu	Jha	970756	Shobhit	D	964036	Ramesh	R	NULL	NULL	NULL	NULL	4
Mike	Dell	NULL	Aravind	I	86721	Raghu	Jha	970756	Shobhit	D	964036	NULL	NULL	NULL	NULL	NULL	NULL	3
Mike	Dell	NULL	Aravind	I	86721	Raghu	Jha	970756	Mahesh	P	964036	NULL	NULL	NULL	NULL	NULL	NULL	3
Mike	Dell	NULL	Aravind	I	86721	Raghu	Jha	970756	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	2
Mike	Dell	NULL	Aravind	I	86721	Rajesh	Moore	970756	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	2
Mike	Dell	NULL	Aravind	I	86721	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	1
Mike	Dell	NULL	Raman	Sapra	86721	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	1
Mike	Dell	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	0


I tried below but i get null values, i don't want to use any PIVOT. I need it to be done with CASE,DECODE or any other function. As i need this query to run in oracle and DB2. We dont have PIVOT in DB2

SELECT 
max( DECODE( EMPLOYEE_LEVEL, 0, T_EMPL_FIRST, NULL ) ) AS first1,
 max( DECODE( EMPLOYEE_LEVEL, 1, T_EMPL_FIRST, NULL ) ) AS first2,
max ( DECODE( EMPLOYEE_LEVEL, 2, T_EMPL_FIRST, NULL ) ) AS first3,
 max( DECODE( EMPLOYEE_LEVEL, 3, T_EMPL_FIRST, NULL ) ) AS first4,
max ( DECODE( EMPLOYEE_LEVEL, 4, T_EMPL_FIRST, NULL ) ) AS first5,
max ( DECODE( EMPLOYEE_LEVEL, 5, T_EMPL_FIRST, NULL ) ) AS first6,
max ( DECODE( EMPLOYEE_LEVEL, 6, T_EMPL_FIRST, NULL ) ) AS first7,
 max ( DECODE( EMPLOYEE_LEVEL,7, T_EMPL_FIRST, NULL ) ) AS first8
 FROM hierarchy_data 
  GROUP BY T_EMPL_FIRST;



Re: SQL help on hierarchy data [message #666255 is a reply to message #666233] Mon, 23 October 2017 09:50 Go to previous messageGo to next message
JPBoileau
Messages: 9
Registered: September 2017
Junior Member
I've simplified it for the sake of brevity.

CREATE TABLE EMPS (EMPLOYEE_ID NUMBER PRIMARY KEY, MANAGER_ID NUMBER, LAST_NAME VARCHAR2(80), FIRST_NAME VARCHAR2(80));

INSERT INTO EMPS VALUES (1, NULL, 'DELL', 'MIKE');
INSERT INTO EMPS VALUES (2, 1, 'SMITH', 'JOHN');
INSERT INTO EMPS VALUES (3, 1, 'DOE', 'JANE');
INSERT INTO EMPS VALUES (4, 3, 'RODGERS', 'MARK');

ALTER TABLE EMPS ADD FOREIGN KEY (MANAGER_ID) REFERENCES EMPS (EMPLOYEE_ID);

COLUMN FIRST_NAME FORMAT A15
COLUMN LAST_NAME FORMAT A15

SELECT E1.LAST_NAME, E1.FIRST_NAME, E2.LAST_NAME, E2.FIRST_NAME, E3.LAST_NAME, E3.FIRST_NAME
FROM EMPS E1, EMPS E2, EMPS E3
WHERE E1.EMPLOYEE_ID = 1 AND
E2.MANAGER_ID(+) = E1.EMPLOYEE_ID AND
E3.MANAGER_ID (+) = E2.EMPLOYEE_ID;
LAST_NAME    FIRST_NAME   LAST_NAME    FIRST_NAME   LAST_NAME    FIRST_NAME
------------ ------------ ------------ ------------ ------------ ------------
DELL         MIKE         DOE          JANE         RODGERS      MARK
DELL         MIKE         SMITH        JOHN
JP

[Updated on: Mon, 23 October 2017 09:51]

Report message to a moderator

Re: SQL help on hierarchy data [message #666259 is a reply to message #666255] Tue, 24 October 2017 06:19 Go to previous messageGo to next message
saipradyumn
Messages: 303
Registered: October 2011
Location: Hyderabad
Senior Member

Hi

Once we know which employee is reporting to whom up to the lowest level starting from highest level we can
format the results with the following example query


SELECT ENAME ,
  empno ,
  mgr,
  level ,
  CONCAT ( LPAD ( ' ', LEVEL*3-3 ), ENAME ) ENAME1 ,
  sys_connect_by_path(ename,'/') path
FROM EMP
  CONNECT BY PRIOR EMPNO = MGR
  START WITH MGR        IS NULL
ORDER BY level
.

Thanks
SaiPradyumn


Re: SQL help on hierarchy data [message #666268 is a reply to message #666259] Tue, 24 October 2017 11:57 Go to previous message
srinivas.k2005
Messages: 359
Registered: August 2006
Senior Member
Thanks, i worked out with outer join condition as suggest by team member. Thanks for your help
Previous Topic: virtual private database
Next Topic: Error 500--Internal Server Error while running oracle Reports to print the pdf in 11g R2[11.1.2.2.0]
Goto Forum:
  


Current Time: Sat Nov 18 08:02:22 CST 2017

Total time taken to generate the page: 0.10581 seconds