SQL Queries - CAST Knowledge Base - Queries on metrics and diagnostics - How to get DETAIL and TOTAL procedures

Purpose of Query

The below SQL queries provides the DETAIL and TOTAL procedures of Quality rules.

Applicable CAST Version

Applicable RDBMS

Query for CSS

1. Run the following queries on the CB:

SELECT TT.METRIC_SCOPE_PROCEDURE_NAME AS DetailProc , T.METRIC_ID , T.METRIC_NAME , COALESCE(P.OBJECT_TYPE_ID,0) AS OBJECT_TYPE_ID, VT.METRIC_VALUE_PROCEDURE_NAME AS TotalProc FROM DSS_METRIC_TYPE_TREES TT , DSS_METRIC_VALUE_TYPES VT , DSS_METRIC_TYPES T LEFT OUTER JOIN DSS_METRIC_PARAM_TYPES P ON ( P.METRIC_ID = T.METRIC_ID AND P.PARAM_INDEX = 1 ) WHERE T.METRIC_GROUP = 1 AND TT.METRIC_ID = T.METRIC_ID + 1 AND VT.METRIC_ID = T.METRIC_ID AND VT.METRIC_VALUE_INDEX = 2 AND metric_name LIKE ‘%<METRIC_NAME>%’ ORDER BY METRIC_NAME

   For example the Metric "Private  Fields naming convention - case and character set control" give the following result:

   **Query result example**

   

<table class="wrapped confluenceTable"><colgroup><col/><col/><col/><col/><col/></colgroup><tbody><tr><th class="confluenceTh"><p>detailproc</p><p>character varying(255)</p></th><th class="confluenceTh"><p>metric_id</p><p>integer</p></th><th class="confluenceTh"><p style="text-align: center;">metric_name</p><p>character varying(255)</p></th><th class="confluenceTh"><p>object_type_id</p><p>integer</p></th><th class="confluenceTh"><p>totalproc</p><p>character varying(255</p></th></tr><tr><td class="confluenceTd">DIAG_SCOPE_NETNAM007</td><td class="confluenceTd">3562</td><td class="confluenceTd">Private Fields naming convention - case and character set control</td><td class="confluenceTd">138385</td><td class="confluenceTd">DIAG_DOTNET_ANA_PV_FIELD_TOTAL</td></tr><tr><td class="confluenceTd">DIAG_SCOPE_NETNAM007</td><td class="confluenceTd">3562</td><td class="confluenceTd">Private Fields naming convention - case and character set control</td><td class="confluenceTd">141901</td><td class="confluenceTd">DIAG_DOTNET_ANA_PV_FIELD_TOTAL</td></tr><tr><td class="confluenceTd">DIAG_SCOPE_NETNAM007</td><td class="confluenceTd">3562</td><td class="confluenceTd">Private Fields naming convention - case and character set control</td><td class="confluenceTd">138383</td><td class="confluenceTd">DIAG_DOTNET_ANA_PV_FIELD_TOTAL</td></tr></tbody></table>

2. The **DETAIL** procedure corresponding to the provided metric is given in the **detailproc** column.
3. The **TOTAL** procedure is given in the **totalproc** column.
4. Connect to PG Admin
5. Go to the browser and navigate to the central schema
6. Expand the central schema and then expand functions
7. For pgadmin3, select the function (detail or total procedure from above) and the sql for the procedure will show up in the default sql pane
8. For pgadmin4, select the function (detail or total procedure from above), right click, and then go to 'Scripts→Create Script', and the sql for the procedure will show up in a new tab.
9. Retrieve the script corresponding to each procedure

**Query for Oracle**

```sql

Query result example

Query result interpretation

Query for SQL server

Query result example

Query result interpretation

Notes/comments

Related Pages