Home » SQL & PL/SQL » SQL & PL/SQL » Query Help (Oracle Database 11g 11.2.0.4.0 )
| Query Help [message #657217] |
Wed, 02 November 2016 01:08 |
 |
SADASIVAM
Messages: 16
Registered: October 2016
|
Junior Member |
|
|
Hi,I need query help for below scenario.Please go through the below requirement help to complete.Please let me know if you are not clear and need more information
I have three tables.
Table test2 having all the Employee IDs.
Table test3 having Parent child Relationship with effective date.Based on this given date ,Relationship will change.But in this table we don't have ID if they don't have further parent ID(Top parent).
Consider ID and parent ID is same and date_from and Date_To are Default date(1/1/1000,1/1/9999).In my data ID 1 and 5 are top parent.
Table test4 is configuration table for generating reports.Query should return the value available in this table.
create table test2 (id Number) ;
Insert into test2 values(1);
Insert into test2 values(5);
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','30-MAR-2016');
Insert into test3 values(2,5,'01-APR-2016','31-DEC-2016');
Insert into test3 values(3,2,'01-JAN-2016','30-DEC-2016');
Insert into test3 values(4,2,'01-JAN-2016','31-DEC-2016');
Create Table Test4(Id Number);
Insert into test4 values(1);
Insert into test4 values(5);
Table test4 is configured with one or more IDS.Here I need to retrieve all the Child's for those ID's mapped in test3.
One more points need to consider is,all the IDs configured in this table are parents .For example ID 1 is parent of ID 2 ,ID 2 is parent of ID 3.If I configure both ID 1 and ID 2 in test4 table then my result should be
1 01/01/1000 31/12/9999 --Parent ID with default date
2 01/01/1000 31/12/9999 --Consider this also parent. Don't consider from Linkage table.
3 01/01/2016 30/12/2016 --This is not configured and this is child of 2.
4 01/01/2016 31/12/2016 --This is not configured and this is child of 2.
Case1 : Only ID 1 configured in test4.My Input date is 30-MAR-16
1 1/1/1000 1/1/9999 --Configured ID should come with default date
2 1/1/2006 30/03/2016 --First Level child
3 1/1/2006 30/12/2016 --Second Level child.Child of ID 2
4 1/1/2006 31/12/2016 --Second Level child.Child of ID 2
Case2 : Only ID 1 configured in test4.My Input date is 01-APR-16
1 1/1/1000 1/1/9999 --Configured ID should come with default date
Next level child is not within that date range.So Further linkages are not there.
Case3: ID 1,2 configured in test4.My Input date is 30-MAR-16
1 1/1/1000 1/1/9999 --Configured ID should come with default date
2 1/1/1000 1/1/9999 --Configured ID should come with default date.
Eventhough effective data available in test3,if it is configured means it should consider default date.
Basically effective date should consider for child linkage only.
3 1/1/2006 30/12/2016 --Child of ID 2
4 1/1/2006 31/12/2016 --Child of ID 2
Case4 : ID 1,2 configured in test4.My Input date is 01-APR-16
1 1/1/1000 1/1/9999 --Configured ID should come with default date
2 1/1/1000 1/1/9999 --Configured ID should come with default date.
Eventhough effective data available in test3,if it is configured means it should consider default date.
3 1/1/2006 30/12/2016 --Child of ID 2
4 1/1/2006 31/12/2016 --Child of ID 2
Case5: ID 5 configured in test4.My Input date is 30-MAR-16
1 1/1/1000 1/1/9999 --Configured ID should come with default date
Next level child is not within that date range.So Further linkages are not there.
Case5 : ID 5 configured in test4.My Input date is 01-APR-16
1 1/1/1000 1/1/9999 --Configured ID should come with default date
2 1/4/2016 31/12/9999 --Configured ID should come with default date.
Eventhough effective data available in test3,if it is configured means it should consider default date.
3 1/1/2006 30/12/2016 --Child of ID 2.Suppose this effective_to is upto 31-MAR-16 then this wont be there.
4 1/1/2006 31/12/2016 --Child of ID 2
Here I want result for all the IDs configured in test4 should come directly with defalut efective dates. '01-JAN-1000' and '01-JAN-9999'
along with that I need to check individual ID in test4 and get all the child with effective date is within the passing date.
With below query I have some issue.I have configured 1,5 in test4 and passing date as '31-MAR-2016'.So I am expecting result as,
1 1/1/1000 1/1/9999
5 1/1/1000 1/1/9999
But I am getting below row also.
4 1/1/2016 12/31/2016
Here Upto 30 march relation ship is 1 -> 2 -> 4 and from 1st Apr 5->2->4.But for 31st March No relationship.Can you please analyze the below query and change it to work for this scenario.
My Current below query I have some issue.I have configured 1,5 in test4 and passing date as '31-MAR-2016'.So I am expecting result as,
1 1/1/1000 1/1/9999
5 1/1/1000 1/1/9999
But I am getting below row also.
4 1/1/2016 12/31/2016
Select Distinct Id,Date_From,Date_To
From
(
Select
X.Id,
Nvl(Date_From,TO_DATE('01-JAN-1000','DD-MON-YYYY')) Date_From ,
Nvl(Date_To,TO_DATE('01-JAN-9999','DD-MON-YYYY')) Date_To
From
(With Base_Set As
(Select Distinct A.Id
From Test4 A,
Test2 B
Where A.Id = B.Id
)
Select
A.Id,
C.Effective_From Date_From,
C.Effective_To Date_To
From
Test2 A,Base_Set B,Test3 C
Where A.Id <> Nvl(C.Parent_Id,A.Id)
And C.Id = A.Id
And C.Id Not In(Select Id From Base_Set)
And :Dt Between Nvl(C.Effective_From,TO_DATE('01-JAN-1000','DD-MON-YYYY'))
And Nvl(C.Effective_To,TO_DATE('01-JAN-9999','DD-MON-YYYY'))
Start With C.Parent_Id = B.Id
Connect By C.Parent_Id = Prior A.Id
) X
Union All
Select Distinct
B.Id,
TO_DATE('01-JAN-1000','DD-MON-YYYY') Date_From,
TO_DATE('01-JAN-9999','DD-MON-YYYY') Date_To
From Test4 A,
Test2 B
Where A.Id = B.Id)
Also I need one more change.When i have the details to populate this query in process ,i dont have :DT to filter.So i need to store the details in to work table and later stage I have to filter the data using :DT something like below
Select * From (
Select Distinct Id,Date_From,Date_To
From
(
Select
X.Id,
Nvl(Date_From,TO_DATE('01-JAN-1000','DD-MON-YYYY')) Date_From ,
Nvl(Date_To,TO_DATE('01-JAN-9999','DD-MON-YYYY')) Date_To
From
(With Base_Set As
(Select Distinct A.Id
From Test4 A,
Test2 B
Where A.Id = B.Id
)
Select
A.Id,
C.Effective_From Date_From,
C.Effective_To Date_To
From
Test2 A,Base_Set B,Test3 C
Where A.Id <> Nvl(C.Parent_Id,A.Id)
And C.Id = A.Id
And C.Id Not In(Select Id From Base_Set)
/* And :Dt Between Nvl(C.Effective_From,TO_DATE('01-JAN-1000','DD-MON-YYYY'))
And Nvl(C.Effective_To,TO_DATE('01-JAN-9999','DD-MON-YYYY'))*/
Start With C.Parent_Id = B.Id
Connect By C.Parent_Id = Prior A.Id
) X
Union All
Select Distinct
B.Id,
TO_DATE('01-JAN-1000','DD-MON-YYYY') Date_From,
TO_DATE('01-JAN-9999','DD-MON-YYYY') Date_To
From Test4 A,
Test2 B
Where A.Id = B.Id) ) temp_table
Where :Dt Between Nvl(Date_From,TO_DATE('01-JAN-1000','DD-MON-YYYY'))
And Nvl(Date_To,TO_DATE('01-JAN-9999','DD-MON-YYYY'))
|
|
|
|
Goto Forum:
Current Time: Tue Apr 23 02:16:57 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
