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

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

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

   detailproc character varying(255)	metric_id integer	metric_name character varying(255)	object_type_id integer	totalproc character varying(255
   DIAG_SCOPE_NETNAM007	3562	Private Fields naming convention - case and character set control	138385	DIAG_DOTNET_ANA_PV_FIELD_TOTAL
   DIAG_SCOPE_NETNAM007	3562	Private Fields naming convention - case and character set control	141901	DIAG_DOTNET_ANA_PV_FIELD_TOTAL
   DIAG_SCOPE_NETNAM007	3562	Private Fields naming convention - case and character set control	138383	DIAG_DOTNET_ANA_PV_FIELD_TOTAL

   
   

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