SQL Queries - CAST Measure Base - Checking and removing corruptions - Snapshot containing quality rules with zero total check

Purpose of Query

This query lists the snapshots containing Quality rules with a total check equal to zero.

Applicable CAST Version

Applicable RDBMS

Query for CSS

Get the snapshot ID and count of QR

select s.SNAPSHOT_ID as Snapshot , count(1) as count
  from DSS_SNAPSHOTS s 
 where exists ( 
  select dmr.SNAPSHOT_ID from DSS_METRIC_RESULTS dmr 
    where dmr.SNAPSHOT_ID = s.SNAPSHOT_ID 
    and dmr.METRIC_VALUE_INDEX = 2 
    and dmr.METRIC_NUM_VALUE = 0 
    and dmr.METRIC_ID in (select dmt.METRIC_ID from DSS_METRIC_TYPES dmt where dmt.METRIC_GROUP = 1) 
    and dmr.OBJECT_ID in (select dos.OBJECT_ID from DSS_OBJECTS dos where dos.OBJECT_TYPE_ID = -102) 
      ) 
  and s.CONSOLIDATION_MODE = -1
group by s.SNAPSHOT_ID

Query result example

Snapshot count

--------   ------  
1          2  
2          2

Query result interpretation

The results that you will get are snapshot ID and count of QR having a total check equal to zero

Correct the inconsistencies

Execute the below queries in the same order for each snapshot ID that is inconsistent in an increasing order. This means if you have snapshots 1 , 2 and 3 that are inconsistent, then you have to execute Query 1 , Query 2 and Query 3 for snapshot ID 1 followed by 2 and then 3.

1. Query 1

select DSS_PROPAGATE_RESULTS_TO_APP(<snapshot_ID>);

2. Query 2

   ```sql
delete
from DSS_METRIC_RESULTS
where (SNAPSHOT_ID, OBJECT_ID, METRIC_ID) in (select dmr.SNAPSHOT_ID, dmr.object_id, dmr.metric_id
      from DSS_METRIC_RESULTS dmr 
      where dmr.METRIC_VALUE_INDEX = 2 
        and dmr.METRIC_NUM_VALUE = 0 
        and dmr.METRIC_ID in (select dmt.METRIC_ID from DSS_METRIC_TYPES dmt where dmt.METRIC_GROUP = 1) 
        and dmr.OBJECT_ID in (select dos.OBJECT_ID from DSS_OBJECTS dos where dos.OBJECT_TYPE_ID = -102) 
        )

3. Query 3

select DSS_CLEANUP_COMPUTE_METRIC( <snapshot_ID> );

**Query for Oracle**

#### Get the snapshot ID and count of QR

```sql
select s.SNAPSHOT_ID as Snapshot , count(1) as count
  from DSS_SNAPSHOTS s 
 where exists ( 
  select dmr.SNAPSHOT_ID from DSS_METRIC_RESULTS dmr 
    where dmr.SNAPSHOT_ID = s.SNAPSHOT_ID 
    and dmr.METRIC_VALUE_INDEX = 2 
    and dmr.METRIC_NUM_VALUE = 0 
    and dmr.METRIC_ID in (select dmt.METRIC_ID from DSS_METRIC_TYPES dmt where dmt.METRIC_GROUP = 1) 
    and dmr.OBJECT_ID in (select dos.OBJECT_ID from DSS_OBJECTS dos where dos.OBJECT_TYPE_ID = -102) 
      ) 
  and s.CONSOLIDATION_MODE = -1
group by s.SNAPSHOT_ID

Query result example

Snapshot count

--------   ------  
1          2  
2          2

Query result interpretation

The results that you will get are snapshot ID and count of QR having a total check equal to zero.

Correct the inconsistencies

Execute the below queries in the same order for each snapshot ID that is inconsistent in an increasing order. This means if you have snapshots 1 , 2 and 3 that are inconsistent, then you have to execute Query1 , Query2 and Query3 for snapshotID 1 followed by 2 and then 3.

1. Query 1

select DSS_PROPAGATE_RESULTS_TO_APP(<snapshot_ID>);

2. Query 2

   ```sql
delete
from DSS_METRIC_RESULTS
where (SNAPSHOT_ID, OBJECT_ID, METRIC_ID) in (select dmr.SNAPSHOT_ID, dmr.object_id, dmr.metric_id
      from DSS_METRIC_RESULTS dmr 
      where dmr.METRIC_VALUE_INDEX = 2 
        and dmr.METRIC_NUM_VALUE = 0 
        and dmr.METRIC_ID in (select dmt.METRIC_ID from DSS_METRIC_TYPES dmt where dmt.METRIC_GROUP = 1) 
        and dmr.OBJECT_ID in (select dos.OBJECT_ID from DSS_OBJECTS dos where dos.OBJECT_TYPE_ID = -102) 
        )

3. Query 3

select DSS_CLEANUP_COMPUTE_METRIC( <snapshot_ID> );

**Query for SQL server**

#### Get the snapshot ID and count of QR

```sql
select s.SNAPSHOT_ID as Snapshot , count(1) as count
  from DSS_SNAPSHOTS s 
 where exists ( 
  select dmr.SNAPSHOT_ID from DSS_METRIC_RESULTS dmr 
    where dmr.SNAPSHOT_ID = s.SNAPSHOT_ID 
    and dmr.METRIC_VALUE_INDEX = 2 
    and dmr.METRIC_NUM_VALUE = 0 
    and dmr.METRIC_ID in (select dmt.METRIC_ID from DSS_METRIC_TYPES dmt where dmt.METRIC_GROUP = 1) 
    and dmr.OBJECT_ID in (select dos.OBJECT_ID from DSS_OBJECTS dos where dos.OBJECT_TYPE_ID = -102) 
      ) 
  and s.CONSOLIDATION_MODE = -1
group by s.SNAPSHOT_ID

Query result example

Snapshot count

--------   ------  
1          2  
2          2

Query result interpretation

The results that you will get are snapshot ID and count of QR having a total check equal to zero.

Correct the inconsistencies

Execute the below queries in the same order for each snapshot ID that is inconsistent in an increasing order. This means if you have snapshots 1 , 2 and 3 that are inconsistent, then you have to execute Query 1 , Query 2 and Query 3 for snapshot ID 1 followed by 2 and then 3.

1. Query 1

select DSS_PROPAGATE_RESULTS_TO_APP(<snapshot_ID>);

2. Query 2

   ```sql
delete
from DSS_METRIC_RESULTS
where (SNAPSHOT_ID, OBJECT_ID, METRIC_ID) in (select dmr.SNAPSHOT_ID, dmr.object_id, dmr.metric_id
      from DSS_METRIC_RESULTS dmr 
      where dmr.METRIC_VALUE_INDEX = 2 
        and dmr.METRIC_NUM_VALUE = 0 
        and dmr.METRIC_ID in (select dmt.METRIC_ID from DSS_METRIC_TYPES dmt where dmt.METRIC_GROUP = 1) 
        and dmr.OBJECT_ID in (select dos.OBJECT_ID from DSS_OBJECTS dos where dos.OBJECT_TYPE_ID = -102) 
        )

3. Query 3

select DSS_CLEANUP_COMPUTE_METRIC( <snapshot_ID> );

**Notes/comments**

**Related Pages**