Hi Gurus,
I have emp table where wants to display data in their emp-manager relationship.
So I have used Hierarchical Query to achieve the result.
Table creation script
=====================
create table emp_test
(
EMPNO NUMBER(4),
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
);
Data script
===========
Insert into emp_test (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7369,'SMITH','ANALYST',7902,to_date('17-DEC-80','DD-MON-RR'),800,null,20);
Insert into emp_test (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7499,'ALLEN','SALESMAN',7698,to_date('22-Feb-81','DD-MON-RR'),1600,300,30);
Insert into emp_test (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7521,'WARD','SALESMAN',7698,to_date('20-Feb-81','DD-MON-RR'),1250,500,30);
Insert into emp_test (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7566,'JONES','MANAGER',7839,to_date('02-APR-81','DD-MON-RR'),2975,null,20);
Insert into emp_test (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7654,'MARTIN','SALESMAN',7698,to_date('8-Sep-81','DD-MON-RR'),1250,1400,30);
Insert into emp_test (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7698,'BLAKE','MANAGER',7839,to_date('01-MAY-81','DD-MON-RR'),2850,null,30);
Insert into emp_test (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7782,'CLARK','MANAGER',7839,to_date('24-Apr-81','DD-MON-RR'),2450,null,10);
Insert into emp_test (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7788,'SCOTT','ANALYST',7566,to_date('19-APR-87','DD-MON-RR'),3000,null,20);
Insert into emp_test (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7839,'KING','PRESIDENT',null,to_date('17-NOV-81','DD-MON-RR'),5000,null,10);
Insert into emp_test (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7844,'TURNER','SALESMAN',7698,to_date('28-Sep-81','DD-MON-RR'),1500,0,30);
Insert into emp_test (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7700,'LARRY','SALESMAN',7698,to_date('28-Sep-81','DD-MON-RR'),1500,0,30);
Insert into emp_test (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7876,'ADAMS','CLERK',7788,to_date('23-MAY-87','DD-MON-RR'),1100,null,20);
Insert into emp_test (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7900,'JAMES','CLERK',7698,to_date('03-DEC-81','DD-MON-RR'),950,null,30);
Insert into emp_test (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7902,'FORD','ANALYST',7566,to_date('03-DEC-81','DD-MON-RR'),3000,null,20);
Insert into emp_test (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7934,'MILLER','CLERK',7782,to_date('23-JAN-82','DD-MON-RR'),1300,null,10);
I am using below query for the same
SELECT LPAD ( '.'
, 2 \* (LEVEL - 1)
, '.'
)|| ename "EMPLOYEE",
EMPNO,
E.HIREDATE
FROM emp_test e
START WITH MGR IS NULL
CONNECT BY PRIOR empno = mgr;
But Output I m getting as below
But I want output as below i.e. when two employee at same level in hierarchy then they should be sorted based on their hire date ascending.
example JONES with hire date 2-Apr-81, BLAKE with hire date 1-May-81 and CLARK with hire date 24-Apr-81 they are at same level child of king, but if we arrange based on their hire date ascending.
Then Jones will be first, Clark will be 2nd and Blake will be 3rd.
Similarly, Larry and James those child of Blake. But when two employee having same hire date then they should be sorted based on their emp id. example Larry and Turner having same hire date but Larry's emp id is 7770 and Turner emp id is 7844.
i.e. whenever two employee at same level then they should be sorted on hire date and then emp id irrespective of whether employee at leaf level or intermediate node level.. So expected out\put will be below
Thanking in advance
Ram