DB2 Correlated Sub Query

 

create or replace view TEST.V_P1RP_EV_TO_RELATEDRR
as
-- We need to find the last P1 RR ADD UPD operation immediately preceeding
-- each other ADD UPD event type
-- this is for when a NON RR event column is populated from the 'associated' RR record
select P1RPEV.ORGANISATION_CODE as P1RPRRR_ORGID
 ,P1RPEV.BATCH_RECEIVED_BATCH_ID as P1RPRRR_BID
 ,P1RPEV.REF_CORRELATION_ID as P1RPRRR_EV_CORRID
-- ,P1RPEV.REF_LOCAL_REF_IDENTIFIER as P1RPRRR_LRID
-- ,P1RPEV.REF_CURRENT_EVENT_ID as P1RPRRR_EVID
 ,P1RPRR.REF_CORRELATION_ID as P1RPRRR_RR_CORRID
from NPFP1.NPF_REFERRAL_PROCESS P1RPEV
join NPFP1.NPF_REFERRAL_PROCESS P1RPRR
 on P1RPRR.ORGANISATION_CODE = P1RPEV.ORGANISATION_CODE
 and P1RPRR.REF_LOCAL_REF_IDENTIFIER = P1RPEV.REF_LOCAL_REF_IDENTIFIER
 and P1RPRR.EVENT_DOMAIN_ID = 1 and P1RPRR.EVENT_OPERATION_ID < 3
where 1 = 1
-- and P1RPEV.REF_CORRELATION_ID = 'NPF-256BPUPDCA0049'
and P1RPRR.REF_TRANS_TS =
 (
 select MAX(REF_TRANS_TS) as MAXTS
 from NPFP1.NPF_REFERRAL_PROCESS RP
 where RP.ORGANISATION_CODE = P1RPEV.ORGANISATION_CODE
  and RP.REF_LOCAL_REF_IDENTIFIER = P1RPEV.REF_LOCAL_REF_IDENTIFIER -- **Correlated**
  and RP.EVENT_DOMAIN_ID = 1 and RP.EVENT_OPERATION_ID < 3
  and RP.REF_TRANS_TS <= P1RPEV.REF_TRANS_TS -- **Correlated**
 )@