Home » SQL & PL/SQL » SQL & PL/SQL » Query Help (Oracle 11g)
Query Help [message #656701] Sat, 15 October 2016 00:01 Go to next message
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 #656703 is a reply to message #656701] Sat, 15 October 2016 01:13 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to rhe forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

Your result and requirements are inconsistent:
Quote:
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.
Quote:
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
But 1 is an employee with no manager ("Top parent").
It seems you want not who is his manager but who are under him.
Please clarify.

Re: Query Help [message #656704 is a reply to message #656703] Sat, 15 October 2016 01:28 Go to previous messageGo to next message
SADASIVAM
Messages: 16
Registered: October 2016
Junior Member
Hi,
Thanks for reply.In linkage table , the data wont be there if he is top parent.But while retrieving data from Query , We need to get the top parent also with parent ID as same employee ID.Because I need current hierarchy,all upper level hierarchy and one level lower level hierarchy for given ID.So in final result we should apply proper level.In this case he don't have any upper level but one lower level(id 2).
So the final output is ID.1-Parent.1,ID.2-Parent.1.If we apply level for this ID.1-Parent.1 is first level and ID.2-Parent.1 is second level.
Please let me know if you need further input.
Re: Query Help [message #656705 is a reply to message #656703] Sat, 15 October 2016 01:31 Go to previous messageGo to next message
SADASIVAM
Messages: 16
Registered: October 2016
Junior Member
One more point from your question.I want who is manager also.That is current hierarchy .Here i mean current hierarchy is passed ID
and consider from that upper and lower hierarchy.

Thanks.
Re: Query Help [message #656706 is a reply to message #656705] Sat, 15 October 2016 01:35 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Once again which one is true:

Quote:
I need current hierarchy,all upper level hierarchy and one level lower level hierarchy for given ID.
Quote:
i mean current hierarchy is passed ID and consider from that upper and lower hierarchy.
One level lower or the whole lower hierarchy?

Re: Query Help [message #656708 is a reply to message #656706] Sat, 15 October 2016 01:41 Go to previous messageGo to next message
SADASIVAM
Messages: 16
Registered: October 2016
Junior Member
Sorry.I missed that.Only one lower level Hierarchy.

For ID 1 ,
-no upper level-
ID.1 - Parent ID 1 - Level 1 -Current level - required
ID.2 - Parent ID 1 - Level 2 -first lower level - required
ID.3 - Parent ID 3 - Level 3 -Second lower level -Not required
ID.4 - Parent ID 3 - Level 3 -Second lower level -Not required

For ID 2 ,

ID.1 - Parent ID 1 - Level 1 -first upper level - required
ID.2 - Parent ID 1 - Level 2 -current level - required
ID.3 - Parent ID 3 - Level 3 -first lower level -required
ID.4 - Parent ID 3 - Level 3 -first lower level -required

For ID 3 ,

ID.1 - Parent ID 1 - Level 1 -Second upper level - required
ID.2 - Parent ID 1 - Level 2 -First upper level - required
ID.3 - Parent ID 3 - Level 3 -Current level -required

For ID 4 ,

ID.1 - Parent ID 1 - Level 1 -Second upper level - required
ID.2 - Parent ID 1 - Level 2 -First upper level - required
ID.4 - Parent ID 3 - Level 3 -Current level -required
Re: Query Help [message #656710 is a reply to message #656706] Sat, 15 October 2016 01:44 Go to previous messageGo to next message
SADASIVAM
Messages: 16
Registered: October 2016
Junior Member
Type mistake

For ID 1 ,
-no upper level-
ID.1 - Parent ID 1 - Level 1 -Current level - required
ID.2 - Parent ID 1 - Level 2 -first lower level - required
ID.3 - Parent ID 2 - Level 3 -Second lower level -Not required
ID.4 - Parent ID 2 - Level 3 -Second lower level -Not required

For ID 2 ,

ID.1 - Parent ID 1 - Level 1 -first upper level - required
ID.2 - Parent ID 1 - Level 2 -current level - required
ID.3 - Parent ID 2 - Level 3 -first lower level -required
ID.4 - Parent ID 2 - Level 3 -first lower level -required

For ID 3 ,

ID.1 - Parent ID 1 - Level 1 -Second upper level - required
ID.2 - Parent ID 1 - Level 2 -First upper level - required
ID.3 - Parent ID 2 - Level 3 -Current level -required

For ID 4 ,

ID.1 - Parent ID 1 - Level 1 -Second upper level - required
ID.2 - Parent ID 1 - Level 2 -First upper level - required
ID.4 - Parent ID 2 - Level 3 -Current level -required
Re: Query Help [message #656714 is a reply to message #656703] Sat, 15 October 2016 01:50 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
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 #656720 is a reply to message #656718] Sat, 15 October 2016 05:12 Go to previous messageGo to next message
SADASIVAM
Messages: 16
Registered: October 2016
Junior Member
Thanks a lot Michel
Re: Query Help [message #656731 is a reply to message #656718] Sun, 16 October 2016 04:05 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
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 #656734 is a reply to message #656733] Sun, 16 October 2016 04:48 Go to previous messageGo to next message
SADASIVAM
Messages: 16
Registered: October 2016
Junior Member
Thank you very much.
Re: Query Help [message #657279 is a reply to message #656733] Fri, 04 November 2016 04:03 Go to previous message
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

Previous Topic: How to format JSON column
Next Topic: summation of multiple values for a particular at specific period
Goto Forum:
  


Current Time: Sat Feb 24 17:22:43 CST 2018

Total time taken to generate the page: 0.01237 seconds