CAST Engineering Dashboard - Violations - Duplicate Violation Details - Multiple display of violation status in the Violation Info tab

Violation status is displayed multiple times for all the metrics in Violation Info as added,added,added,added. Please see the below screenshot

The violation status is displayed multiple times because there exists duplicate values in the dss_violation_statuses table.

The result of below query ran on Central repository shows that there are duplicates :

select SNAPSHOT_ID,
DIAG_ID,
OBJECT_ID, count(1)
from DSS_VIOLATION_STATUSES
group by SNAPSHOT_ID, DIAG_ID, OBJECT_ID
having count(1) > 1
order by SNAPSHOT_ID, DIAG_ID, OBJECT_ID;

1. Run the below script on Central repository to remove duplicates (Please perform a backup of the central schema before running this query):
   
   

   insert into DSS_VIOLATION_STATUSES
   (SNAPSHOT_ID,
   DIAG_ID,
   OBJECT_ID,
   VIOLATION_STATUS)
   select SNAPSHOT_ID + 99999,
   DIAG_ID,
   OBJECT_ID,
   min(VIOLATION_STATUS)
   from DSS_VIOLATION_STATUSES
   group by SNAPSHOT_ID, DIAG_ID, OBJECT_ID
   having count(1) > 1;
   delete from DSS_VIOLATION_STATUSES
   where SNAPSHOT_ID || '$' || DIAG_ID || '$' || OBJECT_ID in (select SNAPSHOT_ID || '$' || DIAG_ID || '$' || OBJECT_ID
   from DSS_VIOLATION_STATUSES
   group by SNAPSHOT_ID, DIAG_ID, OBJECT_ID
   having count(1) > 1
   );
   update DSS_VIOLATION_STATUSES
   set SNAPSHOT_ID = SNAPSHOT_ID - 99999
   where SNAPSHOT_ID > 99999;