SQL Queries - Common SQL Queries - Queries on Modules - How to get the count of objects in the modules based on its type

Purpose of Query

This query provides a count of objects by type for modules.

Applicable CAST Version

Applicable RDBMS

Query for CSS

SELECT   cpm.OBJECT_ID  ,
         cpm.OBJECT_NAME,
         s.SUBSET_ID    ,
         s.SUBSET_NAME  ,
         t.IdTyp        ,
         t.typNam       ,
         t.TypDsc       ,
         COUNT(pso.OBJECT_ID)
FROM     [mngt].cms_portf_module cpm
         JOIN [local].PMC_SUBSETS s
         ON       s.SUBSET_NAME = 'CMS_MOD__'
                           || cpm.OBJECT_ID
                           || '_Preparation2'
         JOIN [local].PMC_SUBSET_OBJECTS pso
         ON       pso.SUBSET_ID = s.SUBSET_ID
         JOIN [local].Keys k
         ON       k.IdKey = pso.OBJECT_ID
         JOIN [local].Typ t
         ON       t.IdTyp = k.ObjTyp
GROUP BY cpm.OBJECT_ID  ,
         cpm.OBJECT_NAME,
         s.SUBSET_ID    ,
         s.SUBSET_NAME  ,
         t.IdTyp        ,
         t.typNam       ,
         t.TypDsc
ORDER BY cpm.OBJECT_ID  ,
         cpm.OBJECT_NAME,
         s.SUBSET_ID    ,
         s.SUBSET_NAME  ,
         t.IdTyp        ,
         t.typNam       ,
         t.TypDsc

Query result example

13474;“ora_test full content”;21;“CMS_MOD__13474_Preparation2”;138014;“CAST_Oracle_Schema”;“Oracle schema”;5
13474;“ora_test full content”;21;“CMS_MOD__13474_Preparation2”;138017;“CAST_Oracle_RelationalTable”;“Oracle table”;45
13474;“ora_test full content”;21;“CMS_MOD__13474_Preparation2”;138159;“CAST_Oracle_Index”;“Oracle index”;56
13474;“ora_test full content”;21;“CMS_MOD__13474_Preparation2”;138183;“CAST_Oracle_View”;“Oracle view”;1

Query result interpretation

For each module a list will be given showing the type of items contained in that module and a count for that type.

Query for Oracle

Query result example

Query result interpretation

Query for SQL server

Query result example

Query result interpretation

Notes/comments

Related Pages