I have data from an Oracle database, where there is a field called MSG_DATE_TIME which is of data type = "timestamp (6) with local time zone".
When the main table updates with a new row, the mapping table updates immediately after, with a few milliseconds delay, like 5 milliseconds. I want to map the FIXING_STATE, so the mapping load would be like this:
MSG_DATE_TIME & ' ' & DESCRIPTION,
The problem is that the MSG_DATE_TIME in the main table and mapping table lag by 5 milliseconds. How can the mapping be done such that the applymap statement can accommodate the 5 millisecond lag time?
The logic would be something like this:
applymap('mapstate',MSG_DATE_TIME within 5 milliseconds & ' ' & DESCRIPTION)
If the corresponding updates to both tables maintain their relative order, you could sequentially number records in each table ordered by timestamp and use that instead of timestamps for matching records. Using ranges would make me nervous about overlaps, althouth that depends on frequency of updates vs lag period.