Appendix G.35

G.35 Handling Temporal Data Duplicates

• Tables
  • D_INTERVAL_TEMPORAL_2
  • D_INTERVAL_TEMPORAL_5
• Views
  • V_SYS_CHK_INT_TMP2_DUPLICATES
  • V_SYS_CHK_INT_TMP2_DUPLICATES2

Estimated Recurrence Time: As required.

Usage of V_SYS_CHK_INT_TMP2_DUPLICATES2

This view does not include UNIT_CODE or RD_TYPE_CODE when grouping records when checking for duplicates. As such, it should be used carefully when determining what records to remove. Here, for example, is a test against the duplicates where we are also checking for a value in RD_VALUE_OUOM (which we want to retain).

select
sys_record_id
from 
(
select
case
when da.rd_value_ouom is not null then v.max_sys_record_id
when db.rd_value_ouom is not null then v.min_sys_record_id
else v.max_sys_record_id
end as sys_record_id
from 
V_SYS_CHK_INT_TMP2_DUPLICATES2 as v
inner join d_interval_temporal_2 as da
on v.min_sys_record_id=da.sys_record_id
inner join d_interval_temporal_2 as db
on v.max_sys_record_id=db.sys_record_id
where
v.data_id= 600
and v.rd_type_code in ( 65 )
) as t
group by
sys_record_id

This uses MIN_SYS_RECORD_ID and MAX_SYS_RECORD_ID which assumes there is only two records which match. The SYS_RECORD_ID returned here indicates the record that is to be deleted (similar to how V_SYS_CHK_INT_TMP2_DUPLICATES_DEL_SRI is implemented).

Last Modified: 2022-06-29