SQL Queries - CAST Central Base - Queries on Metrics - How to check if some metrics have Total Checks equal to 0

Purpose of Query

The query result will show if there are some quality rules or metrics which have Total Checks as zero.

Applicable CAST Version

ReleaseYes/No
8.3.x✅
8.2.x✅

Applicable RDBMS

RDBMSYes/No
Oracle Server✅
Microsoft SQL Server✅
CSS✅

Query for CSS

SELECT COUNT(*)
FROM   DSS_METRIC_RESULTS
WHERE  (
              SNAPSHOT_ID, OBJECT_ID, METRIC_ID
       )
       IN
           (SELECT DISTINCT dmr2.SNAPSHOT_ID,
                            dmr2.OBJECT_ID  ,
                            dmr2.METRIC_ID
           FROM             DSS_METRIC_RESULTS dmr2
                            LEFT JOIN DSS_METRIC_RESULTS dmr0
                            ON               dmr0.SNAPSHOT_ID        = dmr2.SNAPSHOT_ID
                            AND              dmr0.METRIC_ID          = dmr2.METRIC_ID
                            AND              dmr0.OBJECT_ID          = dmr2.OBJECT_ID
                            AND              dmr0.METRIC_VALUE_INDEX = 0
           WHERE            dmr2.METRIC_ID IN
                            (SELECT dmt.METRIC_ID
                            FROM    DSS_METRIC_TYPES dmt
                            WHERE   dmt.METRIC_GROUP = 1
                            )
           AND              dmr2.METRIC_VALUE_INDEX     = 2
           AND              dmr2.METRIC_NUM_VALUE       = 0
           AND              dmr0.METRIC_NUM_VALUE IS NULL
           );

Query for Oracle

Same as that for CSS

Query for SQL server

Same as that for CSS

Notes/comments

Query result example

If count returned is 0, then no metrics exist with Total Checks 0.

If count returned is non-zero, then there are metrics with Total Checks 0. 

Related Pages