Cache polling event - deleting rows from polling table

Boček Petr

Hi please can anybody explain to me? I have a polling table with these records for example

CATALOG_NAME	DATABASE_NAME	OTHER_RESERVED	SCHEME_NAME	TABLE_NAME	UPDATE_TS	UPDATE_TYPE
null	null	null	null	TABLE1	8.11.2019 7:50:51,111111	1
null	null	null	null	TABLE2	8.11.2019 7:50:51,222222	1
null	null	null	null	TABLE3	8.11.2019 7:50:51,111111	1

The event will add new records

CATALOG_NAME	DATABASE_NAME	OTHER_RESERVED	SCHEME_NAME	TABLE_NAME	UPDATE_TS	UPDATE_TYPE
null	null	null	null	TABLE1	8.11.2019 7:50:51,111111	1
null	null	null	null	TABLE2	8.11.2019 7:50:51,222222	1
null	null	null	null	TABLE3	8.11.2019 7:50:52,111111	1
null	null	obis1	null	TABLE1	8.11.2019 7:50:51	1
null	null	obis1	null	TABLE2	8.11.2019 7:50:51	1
null	null	obis1	null	TABLE3	8.11.2019 7:50:52	1

And then performs delete and result look like this:

CATALOG_NAME	DATABASE_NAME	OTHER_RESERVED	SCHEME_NAME	TABLE_NAME	UPDATE_TS	UPDATE_TYPE
null	null	null	null	TABLE1	8.11.2019 7:50:51,111111	1
null	null	null	null	TABLE2	8.11.2019 7:50:51,222222	1
null	null	null	null	TABLE3	8.11.2019 7:50:52,111111	1
null	null	obis1	null	TABLE1	8.11.2019 7:50:51	1
null	null	obis1	null	TABLE2	8.11.2019 7:50:51	1

It perfroms delete just to the datetime which is truncated to seconds and only the unique ones. So my question is how can I achieve that the records will be all deleted after cache purging? Oracle documentation says the UPDATE_TS is unique key, but this is though without the seconds decimals.

Boček Petr

I will answer to myself :-) The key is this sentence.

The Oracle BI Server truncates the timestamps to the number of digits that are defined by FRACTIONAL_SECOND_PRECISION.

So in RPD in physical layer for DB set fractional second precision to value as timestamp so 6 and all works as a charm.