Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Hierarchy query help

sadasivamDec 15 2016 — edited Dec 16 2016

I have the requirement of getting the parent,child relationship.For different period the relationship will change.So for passing input date on that day what is the hierarchy that I need to get.I need result as one lower level from input Id and all upper level.

My Table having below details,

create table test3 (id Number, parent_id Number,effective_From date,effective_to date);

Insert into test3 values(1,6,'01-JAN-2016','31-MAR-2016');

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');

Insert into test3 values(7,3,'01-JAN-2016','31-DEC-2016');

If I pass employee 2 and date 1-jan-2016 then expected result is

ID parent_ID level

6     6       2     --First Upper level

1     6       2     --Second Upper level

2     1       3     --Current level  2

3     2       4     -- First lower level.

4     2       4     -- First lower level.

With the below query I am getting the result up to this level.

with

     lower_level1 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

       ),

     lower_level as (

     Select * From lower_level1

     Union

       select parent_id, effective_From from test3

       where parent_id = :id

         and effective_to =(Select max(effective_to)

                              from test3

                             where parent_id = :id

                               and effective_to < to_date(:dt,'DD-MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH') )

         and 0 = (select count(1) from lower_level1)           

       ),     

     data as (

       select distinct id, parent_id, level lvl,effective_From , effective_to

       from test3

      Where to_date(:dt,'DD-MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH')

                      between effective_From and effective_to     

       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

 

Now I need one more help similar to that requirement.I tried explain the scenario below.Could anyone please help me on this.

In the same way I need to get the hierarchy,but for given date and id if there is no parent or child then get the recent parent/Child relationship previous to given date(Not the feature date datas from given date).

create table test3 (id Number, parent_id Number,effective_From date,effective_to date);

Insert into test3 values(1,2,'01-Apr-2016','31-May-2016');

Insert into test3 values(3,1,'01-Jan-2016','31-Mar-2016');

Insert into test3 values(2,1,'01-Jan-2016','31-Mar-2016');

Insert into test3 values(2,3,'01-Apr-2016','31-May-2016');

Insert into test3 values(5,2,'01-Jun-2016','30-Dec-2016');

Insert into test3 values(6,5,'01-Aug-2016','30-Sep-2016');

Insert into test3 values(7,6,'01-oct-2016','30-nov-2016');

My ID 6 and input date 01-Dec-2016

I need output like

ID 1 -Parent ID 2 - Fourth upper Level

ID 3 -Parent ID 1 - Third upper Level

ID 2 -Parent ID 3 - Second upper Level.For ID 2 having two different parent_Id and Id 3 is recent Head ID for ID 1.

ID 5 -Parent ID 2 - First upper Level.This is active.

ID 6 -Parent ID 5 - Current Level.This is not active on dec but this is recent relation.

ID 7 -Parent ID 6 - First Lower level,This is not active on dec but this is recent relation.

Please reply me if need further input.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jan 13 2017
Added on Dec 15 2016
7 comments
670 views