SQL Queries - CAST Central Base - Queries on quality rules - How to get the total check of a quality rule per module and per snapshot

Purpose of Query

This query get the total check of a quality rule per module and per snapshot.

Applicable CAST Version

Release	Yes/No
8.3.x	✅
8.2.x	✅
8.1.x	✅
8.0.x	✅

Applicable RDBMS

RDBMS	Yes/No
Oracle Server	✅
Microsoft SQL Server	✅
CSS2	✅

Query for CSS

SELECT dob.object_full_name, 
       dmr.snapshot_id, 
       Sum(dmr.metric_num_value) 
FROM   dss_metric_results DMR, 
       dss_module_links DML, 
       dss_objects DOB 
WHERE  dml.object_type_id = 20000 
       AND dml.module_id = dmr.object_id 
       AND dml.snapshot_id = dmr.snapshot_id 
       AND dmr.metric_id = <Quality rule ID>
       AND metric_value_index = 2 
       AND dmr.object_id = DOB.object_id 
GROUP  BY dml.object_id, 
          dmr.metric_value_index, 
          dmr.snapshot_id, 
          dob.object_full_name 
ORDER  BY 2, 
          1

Query result example

“15086 full content_CPP_SUBSET”;11;1

Query result interpretation

The query returns the module name, snapshot_id and the total check

Query for Oracle

SELECT dob.object_full_name, 
       dmr.snapshot_id, 
       Sum(dmr.metric_num_value) 
FROM   dss_metric_results DMR, 
       dss_module_links DML, 
       dss_objects DOB 
WHERE  dml.object_type_id = 20000 
       AND dml.module_id = dmr.object_id 
       AND dml.snapshot_id = dmr.snapshot_id 
       AND dmr.metric_id = <Quality rule ID>
       AND metric_value_index = 2 
       AND dmr.object_id = DOB.object_id 
GROUP  BY dml.object_id, 
          dmr.metric_value_index, 
          dmr.snapshot_id, 
          dob.object_full_name 
ORDER  BY 2, 
          1

Query result example

“15086 full content_CPP_SUBSET”;11;1

Query result interpretation

The query returns the module name, snapshot_id and the total check

Query for SQL server

SELECT dob.object_full_name, 
       dmr.snapshot_id, 
       Sum(dmr.metric_num_value) 
FROM   dss_metric_results DMR, 
       dss_module_links DML, 
       dss_objects DOB 
WHERE  dml.object_type_id = 20000 
       AND dml.module_id = dmr.object_id 
       AND dml.snapshot_id = dmr.snapshot_id 
       AND dmr.metric_id = <Quality rule ID>
       AND metric_value_index = 2 
       AND dmr.object_id = DOB.object_id 
GROUP  BY dml.object_id, 
          dmr.metric_value_index, 
          dmr.snapshot_id, 
          dob.object_full_name 
ORDER  BY 2, 
          1

Query result example

“15086 full content_CPP_SUBSET”;11;1

Query result interpretation

The query returns the module name, snapshot_id and the total check

Notes/comments

Related Pages