Home » SQL & PL/SQL » SQL & PL/SQL » Query Help (Oracle Database 11g 11.2.0.4.0 )
Query Help [message #657058] Thu, 27 October 2016 04:17 Go to next message
SADASIVAM
Messages: 16
Registered: October 2016
Junior Member
Hi,
I need query help on below scenario. Please suggest.

I have below table with ID,and his Parent ID and this relationship effective date.

CREATE TABLE TEST (ID NUMBER, PARENT_ID NUMBER, EFFECTIVE_FROM DATE, EFFECTIVE_TO DATE);

Here I can configure Child as parent or parent as child with different effective date.While entering new record I need to check any overlap between parent and child(or grand Parents/childs).For Example ID 1 is parent of ID 2 between Jan to Mar.In that same Period ID 2 cannot be parent of ID 1.

For example in below example ,overlapping is there.
Insert into test3 values(12,101,TO_DATE('01-JUN-2016','DD-MON-YYYY'),TO_DATE('30-SEP-2016','DD-MON-YYYY'));
Insert into test3 values(101,1001,TO_DATE('01-MAY-2016','DD-MON-YYYY'),TO_DATE('30-JUN-2016','DD-MON-YYYY'));
Insert into test3 values(1001,12,TO_DATE('01-JUN-2016','DD-MON-YYYY'),TO_DATE('30-DEC-2016','DD-MON-YYYY')); [b]--Entering new data.NOT OK[/b]

ID   Parent_ID     Period
---  ---------     -------
12    101         JUN-SEP
101   1001       MAY-JUN
1001  12         JUN-DEC
Here in Jun month 12 -> 101 -> 1001 is active,so 1001-> 12 will be overlapping in Jun.


But this example overlapping not there.
Insert into test3 values(12,101,TO_DATE('01-JUN-2016','DD-MON-YYYY'),TO_DATE('30-SEP-2016','DD-MON-YYYY'));
Insert into test3 values(101,1001,TO_DATE('01-MAY-2016','DD-MON-YYYY'),TO_DATE('30-JUN-2016','DD-MON-YYYY'));
Insert into test3 values(1001,12,TO_DATE('01-JUL-2016','DD-MON-YYYY'),TO_DATE('30-DEC-2016','DD-MON-YYYY')); [b]--Entering new data.OK[/b]
 
 
ID   Parent_ID     Period
---  ---------     -------
12    101         JUN-SEP
101   1001      MAY-JUN
1001  12        JUL-DEC

In the month of July 101 -> 1001 link is disconnected(Active only upto June).So this is Not overlapping and fine.If we consider Jul to Sep month also two linkage is there 12 -> 101 ,1001 -> 12 since 101->1001 linkage disconnected in july.So this is like 1001 -> 12 -> 101.So anyway no overlapping.

Basically I want to assume before inserting the data is available,and keep that is starting point,and check the lower and upper hierarchy.


Can you please suggest me how to find count for ID,Effective date if overlap is there before inserting.Please suggest if this not possible by then and pl/Seql logic.

Please let me know if you need more information

Thanks.
Re: Query Help [message #657101 is a reply to message #657058] Thu, 27 October 2016 18:29 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_12.1.0.2.0> -- starting data:
SCOTT@orcl_12.1.0.2.0> select * from test3
  2  /

        ID  PARENT_ID EFFECTIVE_FROM  EFFECTIVE_TO
---------- ---------- --------------- ---------------
        12        101 Wed 01-Jun-2016 Fri 30-Sep-2016
       101       1001 Sun 01-May-2016 Thu 30-Jun-2016

2 rows selected.

SCOTT@orcl_12.1.0.2.0> -- check for conflict with:
SCOTT@orcl_12.1.0.2.0> -- Insert into test3 values(1001,12,TO_DATE('01-JUN-2016','DD-MON-YYYY'),TO_DATE('30-DEC-2016','DD-MON-YYYY'));
SCOTT@orcl_12.1.0.2.0> with
  2    time_period as
  3  	 (select *
  4  	  from	 test3
  5  	  where  effective_from <= TO_DATE('30-DEC-2016','DD-MON-YYYY')
  6  	  and	 effective_to	>= TO_DATE('01-JUN-2016','DD-MON-YYYY'))
  7  select *
  8  from   time_period
  9  start  with id = 12
 10  connect by prior parent_id = id
 11  intersect
 12  select *
 13  from   time_period
 14  start  with parent_id = 1001
 15  connect by prior id = parent_id
 16  /

        ID  PARENT_ID EFFECTIVE_FROM  EFFECTIVE_TO
---------- ---------- --------------- ---------------
        12        101 Wed 01-Jun-2016 Fri 30-Sep-2016
       101       1001 Sun 01-May-2016 Thu 30-Jun-2016

2 rows selected.

SCOTT@orcl_12.1.0.2.0> -- check for conflict with:
SCOTT@orcl_12.1.0.2.0> -- Insert into test3 values(1001,12,TO_DATE('01-JUL-2016','DD-MON-YYYY'),TO_DATE('30-DEC-2016','DD-MON-YYYY'));
SCOTT@orcl_12.1.0.2.0> with
  2    time_period as
  3  	 (select *
  4  	  from	 test3
  5  	  where  effective_from <= TO_DATE('30-DEC-2016','DD-MON-YYYY')
  6  	  and	 effective_to	>= TO_DATE('01-JUL-2016','DD-MON-YYYY'))
  7  select *
  8  from   time_period
  9  start  with id = 12
 10  connect by prior parent_id = id
 11  intersect
 12  select *
 13  from   time_period
 14  start  with parent_id = 1001
 15  connect by prior id = parent_id
 16  /

no rows selected
Re: Query Help [message #657103 is a reply to message #657101] Fri, 28 October 2016 00:23 Go to previous messageGo to next message
SADASIVAM
Messages: 16
Registered: October 2016
Junior Member
Thank you so much.

I have one clarification.In my oracle forms version with clause is not supporting.So If I change your query like below will it make any difference.

   select *
    from         (select *
          from     test3
          where  effective_from <= TO_DATE('30-DEC-2016','DD-MON-YYYY')
         and     effective_to    >= TO_DATE('01-JUL-2016','DD-MON-YYYY')) 
   start  with id = 12
   connect by prior parent_id = id
   intersect
   select *
   from     (select *
          from     test3
          where  effective_from <= TO_DATE('30-DEC-2016','DD-MON-YYYY')
         and     effective_to    >= TO_DATE('01-JUL-2016','DD-MON-YYYY')) 
  start  with parent_id = 1001
   connect by prior id = parent_id 
Re: Query Help [message #657176 is a reply to message #657103] Fri, 28 October 2016 12:11 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Using inline views instead of a with clause should work just fine and produce the same results.

Re: Query Help [message #657218 is a reply to message #657176] Wed, 02 November 2016 01:11 Go to previous message
SADASIVAM
Messages: 16
Registered: October 2016
Junior Member
Thank you so much for your help.All my cases passed.
Previous Topic: Query Help
Next Topic: Partition Removing
Goto Forum:
  


Current Time: Fri Mar 29 03:44:29 CDT 2024