Get hierarchy for updated records only
I have a couple of tables with some resource data
Resource
----------
rID | rname | updatedstamp
R1 Res1 01-Jul-2015
R2 Res2 01-Jul-2015
R3 Res3 01-Jul-2015
R4 Res4 01-Jul-2015
R5 Res5 01-Jul-2015
R15 Res15 01-Aug-2015
ResourceTree
----------
parID | rID | updatedStamp
---------------------------
NULL R1 01-Jul-2015
R1 R2 01-Aug-2015
R2 R3 01-Jul-2015
R3 R4 01-Jul-2015
R4 R5 01-Jul-2015
R14 R15 01-Jul-2015
I need a select query that will fetch all records updated on or after '01 -Aug-2015 '. Also, I need to get details of child resources of any parent updated in '01 -Aug-2015 '
so in my case I need to get all the records from the resource table updated from 01 Aug 2015. In my case, it will only be R15. In addition, it must also receive data from the Resource_Tree table where the update took place or after Aug 01, 2015. In my case, it will be R2 R3 R4 R5.
results
parid rid rname
R14 R15 Res15
R1 R2 Res2
R2 R3 Res3
R3 R4 Res4
R4 R5 Res5
Request so far
SELECT RT.ParID,R.ID,R.Rname
FROM RESOURCES R, RESOURCETREE RT
WHERE R.RID = RT.RID
And (R.UpdatedStamp >= '01-Aug-2015' or RT.UpdatedStamp >= '01-Aug-2015')
START WITH RT.ParID ='R1' AND
CONNECT BY PRIOR RT.RID=RT.ParID
source to share
This may not be an elegant solution;
with temp_tbl_1 as (
-- get all the "rid" updatedstamp >= '01-Aug-2015' from both tables
select rID
from ResourceTree
where updatedstamp >= '01-Aug-2015'
union all
select rid
from Resource
where updatedstamp >= '01-Aug-2015'
),
temp_tbl_2 as (
select parID, rID
from ResourceTree
START WITH rID in (select distinct rID from temp_tbl_1)
CONNECT BY PRIOR RID = ParID
)
select t.parID, t.rID, r.rname
from temp_tbl_2 t
join Resource r
on r.rID = t.rID
source to share
with x as (
select rid from ResourceTree
where updatedstamp >= '01-Aug-2015'
union
select rid from Resourc
where updatedstamp >= '01-Aug-2015'
)
select r.parid, r.rid, re.rname
from ResourceTree r
left join x on r.parid = x.rid
left join Resourc re on re.rid = r.rid
where r.parid is not null
You select rid
after 01-Aug-15 at cte
and then on the left join other tables in the ResourceTree table.
source to share