SQL Queries - CAST Knowledge Base - Queries on metrics and diagnostics - How to list all Quality Rules and their definition from the Metric Tree

Purpose of Query
This query returns the list of all Quality Rules and their definition from the Metric Tree.
Applicable CAST Version
Release
Yes/No
8.3.x(tick)
8.2.x(tick)
Applicable RDBMS
RDBMS
Yes/No
Oracle Server(question)
Microsoft SQL Server(question)
CSS(tick)
Query for CSS
 SELECT COALESCE(tech.techno_type_name, 'AllTechno') AS technology_name, 
       COALESCE(PT.object_type_id, 0)               AS technology_id, 
       T.metric_id, 
       T.metric_name, 
       COALESCE(p.idprop, 0)                        AS Property_ID, 
       COALESCE(p.dsc, ' ')                         AS Property_Name, 
       COALESCE(T.scope_id, 0), 
       COALESCE(S.setname, 'NONE'), 
       TT.metric_scope_procedure_name               AS detailed_proc_name, 
       VT.metric_value_procedure_name               AS total_proc_name 
FROM   dss_metric_type_trees TT, 
       dss_metric_value_types VT, 
       dss_metric_types T 
       LEFT OUTER JOIN dss_metric_param_types PT 
                    ON PT.metric_id = T.metric_id 
                       AND PT.param_index = 1 
       LEFT OUTER JOIN dss_techno_display_vw tech 
                    ON tech.techno_type_id = PT.object_type_id 
       LEFT OUTER JOIN (SELECT DISTINCT d.dsc, 
                                        p1.idprop 
                        FROM   propattr p1, 
                               propattr p2, 
                               objdscref d 
                        WHERE  p1.idprop = p2.idprop 
                               AND p1.attrnam = 'INF_TYPE' 
                               AND p1.intval = d.inftyp 
                               AND p2.attrnam = 'INF_SUB_TYPE' 
                               AND p2.intval = d.infsubtyp) p 
                    ON p.idprop = T.property_id 
       LEFT OUTER JOIN (SELECT setid, 
                               setname 
                        FROM   set_definitions) S 
                    ON S.setid = T.scope_id 
WHERE  T.metric_group = 1 
       AND T.metric_type != 0 -- Not Deleted 
       AND TT.metric_id = T.metric_id + 1 
       AND VT.metric_id = T.metric_id 
       AND VT.metric_value_index = 2 
       AND EXISTS (SELECT 1 
                   FROM   dss_metric_type_trees x 
                   WHERE  x.metric_id = T.metric_id) 
Query result example

"C++";-3;550;"Class naming convention - case control";0;" ";0;"NONE";"DIAG_SCOPE_CPPNAM001";"DIAG_CPP_ANA_CLASS_TOTAL"
"C++";-3;552;"Method naming convention - case control";0;" ";0;"NONE";"DIAG_SCOPE_CPPNAM002";"DIAG_CPP_ANA_METHODS_TOTAL"
"C++";-3;554;"Constant naming convention - case control";0;" ";0;"NONE";"DIAG_SCOPE_CPPNAM003";"DIAG_CPP_ANA_GLOBALS_TOTAL"
"C++";-3;556;"Macros naming convention - case control";0;" ";0;"NONE";"DIAG_SCOPE_CPPNAM004";"DIAG_CPP_ANA_MACROS_TOTAL"
"C++";-3;558;"Data Members naming convention - case control";0;" ";0;"NONE";"DIAG_SCOPE_CPPNAM005";"DIAG_CPP_ANA_NCDTMEMBERS_TOTAL"
"C++";-3;562;"File naming convention - embedded Class";0;" ";0;"NONE";"DIAG_SCOPE_CPPNAM006";"DIAG_CPP_ANA_FILES_TOTAL"
"C++";-3;564;"Avoid including files other than header files";0;" ";0;"NONE";"DIAG_SCOPE_CPPARCH004";"DIAG_CPP_ANA_FILES_TOTAL"

Query result interpretation
This query returns the list of all Quality Rules and their definition from the Metric Tree, with the following information: metric name, technology, detailed proc name, total proc name and metric id
Query for Oracle
 N/A
Query for SQL server
 N/A
Notes/comments

Query result example