Applymap with time range

[sifatnabil]

Hi,

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:

mapstate:

MAPPING LOAD

     MSG_DATE_TIME & ' ' & DESCRIPTION,

     FIXING_STATE

Resident mappingtable;

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)

Thanks.   

[MarcoWedel · Accepted solution]

maybe you could use intervalmatch instead of mapping load / applymap.

[kuba_michalik]

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.

[#4]

Rahman, May be you can remove the milliseconds from both dates and do apply map if possible.

Otherwise, use the IntervelMatch instead of applymap.