Home » SQL & PL/SQL » SQL & PL/SQL » Query Help (Oracle 11g)
Query Help [message #656701] |
Sat, 15 October 2016 00:01 |
|
SADASIVAM
Messages: 16 Registered: October 2016
|
Junior Member |
|
|
Hi,
I need to write query to get employee manager relationship Hierarchy.
I have below two tables.
create table test2 (id Number) ;
Insert into test2 values(1); --Top parent
Insert into test2 values(5); --Top parent
Insert into test2 values(2);
Insert into test2 values(3);
Insert into test2 values(4);
create table test3 (id Number, parent_id Number,effective_From date,effective_to date);
Insert into test3 values(2,1,'01-JAN-2016','31-MAR-2016');
Insert into test3 values(2,5,'01-APR-2016','31-DEC-2016');
Insert into test3 values(3,2,'01-JAN-2016','31-DEC-2016');
Insert into test3 values(4,2,'01-JAN-2016','31-DEC-2016');
Table test2 having all employee IDs and employee itself may be manager
select * from test2;
ID
----------
1 --Manager
5 --Manager
2 --1st level employee (Team Lead)
3 --2nd level employee
4 --2nd level employee
--In test3 table having employee and his curresponding manager linkage.But if the employee is high level manager
-- and if whe dont have any further manager then that data wont be available in this linkage table(assuming manager of himself).
--Employee 1 and 5 dont have any further manager so those employee ID wont be here.
select * from test3;
ID PARENT_ID EFFECTIVE_FROM EFFECTIVE_TO
---------- ---------- -------------- ------------
2 1 01-JAN-16 31-MAR-16 --Employee 2 working under manager 1 between 01-JAN-16 and 31-MAR-16
2 5 01-APR-16 31-DEC-16 --Employee 2 then changed to manager 5 between01-APR-16 and 31-DEC-16
3 2 01-JAN-16 31-DEC-16 --employee 3 working under employee 2 between 01-JAN-16 and 31-DEC-16
4 2 01-JAN-16 31-DEC-16 --employee 4 also working under employee 2 between 01-JAN-16 and 31-DEC-16
Now my requirement is , if I pass specific employee ID and date to my query it should return the employee and who is his manager in that specific date in hierarchy. That hierarchy should be current,all upper level and one lower level.
For example,
If I pass employee 1 and date 1-jan-2016 then expected result is
ID parent_ID level
--- ---- ----
1 1 1 --Current level parent himself
2 1 2 --one level lower .Here No upper level
If I pass employee 2 and date 1-jan-2016 then expected result is
ID parent_ID level
1 1 1 --Upper level is 1
2 1 2 --Current level 2
3 2 3 -- Current level 3.Emploee 2 is manager of employee 3.
4 2 3 --Current level 3..Emploee 2 is also manager of employee 4.
If I pass employee 3 and date 1-jan-2016 then expected result is
ID parent_ID level
1 1 1 --First upper level 1
2 1 2 --Second upper level 2
3 2 3 -- Current level 3.Emploee 2 is manager of employee 3.FOr him no further lower level.
Sameway If I pass employee 1 and date 1-Apr-2016 then expected result is and so on.
ID parent_ID level
5 5 1 --Current level parent himself
2 1 2 --one level lower .Here No upper level.
I tried to join these two tables,but its not not working as one table dont have data for sometime.Please help me to write this query.
with upper as (
select parent_id, A.id,
max(level) over() - level + 1 lvl
from test2 A, test3 B
WHERE A.ID=B.ID
AND :v_date between effective_From AND effective_to
start with A.id = :n
connect by A.id = prior parent_id
order by 3
)
select * from upper
union all
select parent_id, A.id,
(select max(lvl) from upper) +1 lvl
from test2 A, test3 B
WHERE A.ID=B.ID
AND :v_date between effective_From AND effective_to
AND parent_id = :n;
Thanks in advance.
[Edit MC: add code tags]
[Updated on: Sat, 15 October 2016 01:12] by Moderator Report message to a moderator
|
|
|
|
|
|
|
|
|
Re: Query Help [message #656714 is a reply to message #656703] |
Sat, 15 October 2016 01:50 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Also I forgot to mention: '01-JAN-2016' is NOT a date it is a string, proof:
SQL> select to_date('01-JAN-2016') from dual;
select to_date('01-JAN-2016') from dual
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
When you give INSERT statement you must use the correct datatype, here convert your string to DATE type using TO_DATE with the proper format mask. In addition, as we have not the same language, it is better to use numbers and not names (or specify the language):
SQL> select to_date('01-JAN-2016','DD-MON-YYYY') from dual;
select to_date('01-JAN-2016','DD-MON-YYYY') from dual
*
ERROR at line 1:
ORA-01843: not a valid month
So either:
SQL> select to_date('01-01-2016','DD-MM-YYYY') from dual;
TO_DATE('01-01-2016
-------------------
01/01/2016 00:00:00
or
SQL> select to_date('01-JAN-2016','DD-MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH') from dual;
TO_DATE('01-JAN-201
-------------------
01/01/2016 00:00:00
|
|
|
Re: Query Help [message #656716 is a reply to message #656714] |
Sat, 15 October 2016 01:56 |
|
SADASIVAM
Messages: 16 Registered: October 2016
|
Junior Member |
|
|
Thanks Michel,
Please use below for your testing
Insert into test3 values(2,1,to_date('01-01-2016','DD-MM-YYYY'),to_date('31-03-2016','DD-MM-YYYY'));
Insert into test3 values(2,5,to_date('01-04-2016','DD-MM-YYYY'),to_date('31-12-2016','DD-MM-YYYY'));
Insert into test3 values(3,2,to_date('01-01-2016','DD-MM-YYYY'),to_date('31-12-2016','DD-MM-YYYY'));
Insert into test3 values(4,2,to_date('01-01-2016','DD-MM-YYYY'),to_date('31-12-2016','DD-MM-YYYY'));
|
|
|
Re: Query Help [message #656718 is a reply to message #656716] |
Sat, 15 October 2016 02:41 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Not an easy one given the rows you want to return and level value you want:
SQL> def id=1
SQL> def dt=1-jan-2016
SQL> with
2 lower_level as (
3 select parent_id, effective_From from test3
4 where parent_id = &id
5 and to_date('&dt','DD-MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH')
6 between effective_From and effective_to
7 ),
8 data as (
9 select distinct id, parent_id, level lvl
10 from test3
11 connect by prior parent_id = id
12 and to_date('&dt','DD-MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH')
13 between effective_From and effective_to
14 start with (parent_id, effective_From) in (select * from lower_level)
15 or (id, effective_From) in
16 (select id, effective_From from test3
17 where id = &id
18 and to_date('&dt','DD-MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH')
19 between effective_From and effective_to
20 and not exists (select null from lower_level))
21 ),
22 lmax as (select max(lvl) lmax from data)
23 select id, parent_id, lmax-lvl+2 "LEVEL"
24 from data, lmax
25 union all
26 select &id, &id, 1
27 from dual
28 where exists (select null from lower_level)
29 and not exists (select null from data)
30 union all
31 select parent_id, parent_id, 1
32 from data
33 where lvl = (select lmax from lmax)
34 order by 3, 1
35 /
ID PARENT_ID LEVEL
---------- ---------- ----------
1 1 1
2 1 2
2 rows selected.
SQL> def id=2
SQL> /
ID PARENT_ID LEVEL
---------- ---------- ----------
1 1 1
2 1 2
3 2 3
4 2 3
4 rows selected.
SQL> def id=3
SQL> /
ID PARENT_ID LEVEL
---------- ---------- ----------
1 1 1
2 1 2
3 2 3
3 rows selected.
SQL> def id=4
SQL> /
ID PARENT_ID LEVEL
---------- ---------- ----------
1 1 1
2 1 2
4 2 3
3 rows selected.
[Updated on: Sun, 16 October 2016 14:54] Report message to a moderator
|
|
|
|
Re: Query Help [message #656731 is a reply to message #656718] |
Sun, 16 October 2016 04:05 |
|
SADASIVAM
Messages: 16 Registered: October 2016
|
Junior Member |
|
|
Hi Michel,
I need one clarification to understand the query.Can you please explain in which scenario the below part of code required.
26 select &id, &id, 1
27 from dual
28 where exists (select null from lower_level)
29 and not exists (select null from data)
looks like with my current data its mostly returns blank.your explanation will really helpful to implement in my original query with this logics.
Thanks.
[Updated on: Sun, 16 October 2016 04:26] Report message to a moderator
|
|
|
Re: Query Help [message #656733 is a reply to message #656731] |
Sun, 16 October 2016 04:31 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Line 29 is there for the case (like id=1) where the given id is a top manager (and so is not in the table test3). Line 28 is there to be sure the id exists and has a colleague under him at the date you gave.
Note that the query returns nothing if you give id who is a top manager and has no one under him at the date you give, like id=5 in your example:
SQL> def id=5
SQL> with
2 lower_level as (
3 select parent_id, effective_From from test3
4 where parent_id = &id
5 and to_date('&dt','DD-MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH')
6 between effective_From and effective_to
7 ),
8 data as (
9 select distinct id, parent_id, level lvl
10 from test3
11 connect by prior parent_id = id
12 and to_date('&dt','DD-MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH')
13 between effective_From and effective_to
14 start with (parent_id, effective_From) in (select * from lower_level)
15 or (id, effective_From) in
16 (select id, effective_From from test3
17 where id = &id
18 and to_date('&dt','DD-MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH')
19 between effective_From and effective_to
20 and not exists (select null from lower_level))
21 ),
22 lmax as (select max(lvl) lmax from data)
23 select id, parent_id, lmax-lvl+2 "LEVEL"
24 from data, lmax
25 union all
26 select &id, &id, 1
27 from dual
28 where exists (select null from lower_level)
29 and not exists (select null from data)
30 union all
31 select parent_id, parent_id, 1
32 from data
33 where lvl = (select lmax from lmax)
34 order by 3, 1
35 /
no rows selected
If you want to return the top manager anyway in this case, then replace line 28 as below:
SQL> def id=5
SQL> with
2 lower_level as (
3 select parent_id, effective_From from test3
4 where parent_id = &id
5 and to_date('&dt','DD-MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH')
6 between effective_From and effective_to
7 ),
8 data as (
9 select distinct id, parent_id, level lvl
10 from test3
11 connect by prior parent_id = id
12 and to_date('&dt','DD-MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH')
13 between effective_From and effective_to
14 start with (parent_id, effective_From) in (select * from lower_level)
15 or (id, effective_From) in
16 (select id, effective_From from test3
17 where id = &id
18 and to_date('&dt','DD-MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH')
19 between effective_From and effective_to
20 and not exists (select null from lower_level))
21 ),
22 lmax as (select max(lvl) lmax from data)
23 select id, parent_id, lmax-lvl+2 "LEVEL"
24 from data, lmax
25 union all
26 select &id, &id, 1
27 from dual
28 where exists (select null from test2 where id = &id)
29 and not exists (select null from data)
30 union all
31 select parent_id, parent_id, 1
32 from data
33 where lvl = (select lmax from lmax)
34 order by 3, 1
35 /
ID PARENT_ID LEVEL
---------- ---------- ----------
5 5 1
1 row selected.
[Updated on: Sun, 16 October 2016 14:55] Report message to a moderator
|
|
|
|
Re: Query Help [message #657279 is a reply to message #656733] |
Fri, 04 November 2016 04:03 |
|
SADASIVAM
Messages: 16 Registered: October 2016
|
Junior Member |
|
|
Hi Michel
I have some scenario failed.SO I modified the query you suggested.Can you Please go thorugh this and give your suggestion.
select * from test3
ID PARENT_ID EFFECTIVE_FROM EFFECTIVE_TO
---------- ---------- -------------- ------------
2 1 01-JAN-16 30-MAR-16
2 5 01-APR-16 31-DEC-16
3 2 01-JAN-16 30-MAR-16
4 2 01-JAN-16 31-DEC-16
6 1 01-JAN-16 31-DEC-16
So If I Pass Id as 1 and dt=1-Apr-2016 then Child 6 is only child for Parent 1 in this period.But I am getting child 2 also with this Query.
def id=1
def dt=1-Apr-2016
with
lower_level as (
select parent_id, effective_From from test3
where parent_id = :id
and to_date(:dt,'DD-MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH')
between effective_From and effective_to
),
data as (
select distinct id, parent_id, level lvl,effective_From , effective_to
from test3
connect by prior parent_id = id
and to_date(:dt,'DD-MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH')
between effective_From and effective_to
start with (parent_id, effective_From) in (select * from lower_level)
or (id, effective_From) in
(select id, effective_From from test3
where id = :id
and to_date(:dt,'DD-MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH')
between effective_From and effective_to
and not exists (select null from lower_level))
),
lmax as (select max(lvl) lmax from data)
select id, parent_id, lmax-lvl+2 "LEVEL",effective_From , effective_to
from data, lmax
union all
select parent_id, parent_id, 1,effective_From , effective_to
from data
where lvl = (select lmax from lmax)
order by 3, 1
/
1 1 1 1/1/2016 12/31/2016
1 1 1 1/1/2016 3/30/2016
2 1 2 1/1/2016 3/30/2016
6 1 2 1/1/2016 12/31/2016
So I modified the query with aditional filter and working for my data.But I am not sure how the connect by will impact on this where.
Can you please give me your thoughts that the change is not making any impact on this query.
with
lower_level as (
select parent_id, effective_From from test3
where parent_id = :id
and to_date(:dt,'DD-MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH')
between effective_From and effective_to
),
data as (
select distinct id, parent_id, level lvl,effective_From , effective_to
from test3
[b] Where to_date(:dt,'DD-MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH')
between effective_From and effective_to [/b]
connect by prior parent_id = id
and to_date(:dt,'DD-MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH')
between effective_From and effective_to
start with (parent_id, effective_From) in (select * from lower_level)
or (id, effective_From) in
(select id, effective_From from test3
where id = :id
and to_date(:dt,'DD-MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH')
between effective_From and effective_to
and not exists (select null from lower_level))
),
lmax as (select max(lvl) lmax from data)
select id, parent_id, lmax-lvl+2 "LEVEL",effective_From , effective_to
from data, lmax
union all
select parent_id, parent_id, 1,effective_From , effective_to
from data
where lvl = (select lmax from lmax)
order by 3, 1
/
[Updated on: Fri, 04 November 2016 04:05] Report message to a moderator
|
|
|
Goto Forum:
Current Time: Sat Jun 29 06:34:27 CDT 2024
|