Home » SQL & PL/SQL » SQL & PL/SQL » Hierarchy Query Help (Oracle 11g)
Hierarchy Query Help [message #658475] Thu, 15 December 2016 08:06
SADASIVAM
Messages: 16
Registered: October 2016
Junior Member
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.This hierarchy is one lower level from input Id and all upper level.

I got the solution from below thread.

http://www.orafaq.com/forum/m/656705/#msg_656705

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 this one is recent ID.
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.

My previous query is
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
Previous Topic: ORA-01722: invalid number ORA-02063: preceding line from
Next Topic: Skipping code in a procedure?
Goto Forum:
  


Current Time: Fri Feb 23 19:16:57 CST 2018

Total time taken to generate the page: 0.03927 seconds