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

Created by Amine Benarbia on Feb 11, 2022
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 (tick) 
8.2.x (tick) 
8.1.x (tick) 
8.0.x (tick) 
Applicable RDBMS

 

RDBMS
Yes/No
Oracle Server (tick) 
Microsoft SQL Server (tick) 
CSS2 (tick) 


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