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 |
|
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 |
|
Barbara Boehmer
Messages: 9097 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
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Jun 29 06:30:52 CDT 2024
|