Purpose of Query
This SQL Query helps us to check if the objects are shared among modules. This query will be useful when you see a difference in values between application level and module level.
For instance - the number of violations at application level is not equal to sum of violations at module level
Applicable CAST Version
Release | Yes/No |
|---|---|
| 8.3.x |  |
| 8.2.x | |
| 8.1.x | |
| 8.0.x | |
| 7.3.x | |
Applicable RDBMS
RDBMS | Yes/No |
|---|---|
| Oracle Server | |
| Microsoft SQL Server | |
| CSS2 | |
Query for CSS
Execute the below query on the Central Base by replacing the <snaphshot_id> with your current snapshot_id.
select SNAPSHOT_ID,
count(OBJECT_ID) as OBJECTS_NB,
sum(SHARING_FACTOR) as SHARED_OBJECTS_NB,
sum(NB) as VIOLATIONS,
sum(NB * SHARING_FACTOR) as SHARED_VIOLATIONS
from (select R.SNAPSHOT_ID,
R.OBJECT_ID,
count(distinct DLIM.NEXT_OBJECT_ID) as SHARING_FACTOR,
count(distinct R.METRIC_ID) as NB
from DSS_METRIC_TYPES T
join DSS_METRIC_HISTO_TREE HT
on HT.METRIC_ID = T.METRIC_ID
and T.METRIC_GROUP = 1
-- and HT.METRIC_CRITICAL = 1 --uncomment this line if you want to restrict the results to critical Quality rules
join DSS_METRIC_RESULTS R
on R.METRIC_ID = HT.METRIC_ID + 1
and R.METRIC_VALUE_INDEX = 1
and R.SNAPSHOT_ID = HT.SNAPSHOT_ID
join DSS_LINK_INFO DLI
on DLI.SNAPSHOT_ID = HT.SNAPSHOT_ID
and DLI.LINK_TYPE_ID = 3
and DLI.NEXT_OBJECT_ID = R.OBJECT_ID
join DSS_LINK_INFO DLIM
on DLIM.SNAPSHOT_ID = HT.SNAPSHOT_ID
and DLIM.LINK_TYPE_ID = 1
and DLIM.NEXT_OBJECT_ID = DLI.PREVIOUS_OBJECT_ID
join DSS_OBJECTS dsom
on dsom.OBJECT_ID = dlim.PREVIOUS_OBJECT_ID
and dsom.OBJECT_TYPE_ID = 20000
and dsom.OBJECT_NAME not like '% union content%'
join DSS_OBJECTS dsot
on dsot.OBJECT_ID = dli.PREVIOUS_OBJECT_ID
and dsot.OBJECT_NAME not like '% union content%'
where R.SNAPSHOT_ID = <snapshot_id>
group by R.SNAPSHOT_ID, R.OBJECT_ID
) t
group by SNAPSHOT_ID;
Query result example
select SNAPSHOT_ID,
count(OBJECT_ID) as OBJECTS_NB,
sum(SHARING_FACTOR) as SHARED_OBJECTS_NB,
sum(NB) as VIOLATIONS,
sum(NB * SHARING_FACTOR) as SHARED_VIOLATIONS
from (select R.SNAPSHOT_ID,
R.OBJECT_ID,
count(distinct DLIM.NEXT_OBJECT_ID) as SHARING_FACTOR,
count(distinct R.METRIC_ID) as NB
from DSS_METRIC_TYPES T
join DSS_METRIC_HISTO_TREE HT
on HT.METRIC_ID = T.METRIC_ID
and T.METRIC_GROUP = 1
--and HT.METRIC_CRITICAL = 1 --uncomment this line if you want to restrict the results to critical Quality rules
join DSS_METRIC_RESULTS R
on R.METRIC_ID = HT.METRIC_ID + 1
and R.METRIC_VALUE_INDEX = 1
and R.SNAPSHOT_ID = HT.SNAPSHOT_ID
join DSS_LINK_INFO DLI
on DLI.SNAPSHOT_ID = HT.SNAPSHOT_ID
and DLI.LINK_TYPE_ID = 3
and DLI.NEXT_OBJECT_ID = R.OBJECT_ID
join DSS_LINK_INFO DLIM
on DLIM.SNAPSHOT_ID = HT.SNAPSHOT_ID
and DLIM.LINK_TYPE_ID = 1
and DLIM.NEXT_OBJECT_ID = DLI.PREVIOUS_OBJECT_ID
join DSS_OBJECTS dsom
on dsom.OBJECT_ID = dlim.PREVIOUS_OBJECT_ID
and dsom.OBJECT_TYPE_ID = 20000
and dsom.OBJECT_NAME not like '% union content%'
join DSS_OBJECTS dsot
on dsot.OBJECT_ID = dli.PREVIOUS_OBJECT_ID
and dsot.OBJECT_NAME not like '% union content%'
where R.SNAPSHOT_ID = 8
group by R.SNAPSHOT_ID, R.OBJECT_ID
) t
group by SNAPSHOT_ID;
Result -
"snapshot_id";"objects_nb";"shared_objects_nb";"violations";"shared_violations"
8;9232;9232;18525;18525
Query result interpretation
If the value of the objects_nb column is equal to shared_objects_nb then it means that there are no shared objects. If the value of the objects_nb column is different from shared_objects_nb then it means there are some shared objects.
If there are shared objects then you can get the details with the help of the below query -
select T.SNAPSHOT_ID,
dso.OBJECT_ID,
dso.OBJECT_NAME,
dso.OBJECT_FULL_NAME,
dsom.OBJECT_ID,
dsom.OBJECT_NAME,
dsot.OBJECT_ID,
dsot.OBJECT_NAME,
sum(NB) as violations,
sum(NB*SHARING_FACTOR) as SHARED_VIOLATIONS
from DSS_OBJECTS dso
join ( select R.SNAPSHOT_ID,
R.OBJECT_ID,
count(distinct R.METRIC_ID) as NB,
count(distinct DLIM.PREVIOUS_OBJECT_ID) as SHARING_FACTOR
from DSS_METRIC_TYPES T
join DSS_METRIC_HISTO_TREE HT
on HT.METRIC_ID = T.METRIC_ID
and T.METRIC_GROUP = 1
-- and HT.METRIC_CRITICAL = 1 --uncomment this line if you want to restrict the results to critical Quality rules
join DSS_METRIC_RESULTS R
on R.METRIC_ID = HT.METRIC_ID + 1
and R.METRIC_VALUE_INDEX = 1
and R.SNAPSHOT_ID = HT.SNAPSHOT_ID
join DSS_LINK_INFO DLI
on DLI.SNAPSHOT_ID = HT.SNAPSHOT_ID
and DLI.LINK_TYPE_ID = 3
and DLI.NEXT_OBJECT_ID = R.OBJECT_ID
join DSS_LINK_INFO DLIM
on DLIM.SNAPSHOT_ID = HT.SNAPSHOT_ID
and DLIM.LINK_TYPE_ID = 1
and DLIM.NEXT_OBJECT_ID = DLI.PREVIOUS_OBJECT_ID
join (select *
from DSS_OBJECTS dsom
where dsom.OBJECT_TYPE_ID = 20000
) dsom
on dsom.OBJECT_ID = dlim.PREVIOUS_OBJECT_ID
join DSS_OBJECTS dsot
on dsot.OBJECT_ID = dli.PREVIOUS_OBJECT_ID
and dsot.OBJECT_NAME not like '% union content%'
where R.SNAPSHOT_ID = 8
group by R.SNAPSHOT_ID, R.OBJECT_ID,HT.metric_id
) T
on T.OBJECT_ID = dso.OBJECT_ID
join DSS_LINK_INFO DLI
on DLI.SNAPSHOT_ID = T.SNAPSHOT_ID
and DLI.LINK_TYPE_ID = 3
and DLI.NEXT_OBJECT_ID = T.OBJECT_ID
join DSS_LINK_INFO DLIM
on DLIM.SNAPSHOT_ID = T.SNAPSHOT_ID
and DLIM.LINK_TYPE_ID = 1
and DLIM.NEXT_OBJECT_ID = DLI.PREVIOUS_OBJECT_ID
join DSS_OBJECTS dsom
on dsom.OBJECT_ID = dlim.PREVIOUS_OBJECT_ID
and dsom.OBJECT_TYPE_ID = 20000
and dsom.OBJECT_NAME not like '% union content%'
join DSS_OBJECTS dsot
on dsot.OBJECT_ID = dli.PREVIOUS_OBJECT_ID
and dsot.OBJECT_NAME not like '% union content%'
where T.SHARING_FACTOR > 1
group by t.snapshot_id, dso.object_id,dsom.OBJECT_ID,dsot.object_id
order by T.SNAPSHOT_ID, dso.OBJECT_ID, dso.OBJECT_NAME, dso.OBJECT_FULL_NAME, dsom.OBJECT_ID, dsom.OBJECT_NAME, dsot.OBJECT_ID, dsot.OBJECT_NAME
Query result example
select T.SNAPSHOT_ID,
dso.OBJECT_ID,
dso.OBJECT_NAME,
dso.OBJECT_FULL_NAME,
dsom.OBJECT_ID,
dsom.OBJECT_NAME,
dsot.OBJECT_ID,
dsot.OBJECT_NAME,
sum(NB) as violations,
sum(NB*SHARING_FACTOR) as SHARED_VIOLATIONS
from DSS_OBJECTS dso
join ( select R.SNAPSHOT_ID,
R.OBJECT_ID,
count(distinct R.METRIC_ID) as NB,
count(distinct DLIM.PREVIOUS_OBJECT_ID) as SHARING_FACTOR
from DSS_METRIC_TYPES T
join DSS_METRIC_HISTO_TREE HT
on HT.METRIC_ID = T.METRIC_ID
and T.METRIC_GROUP = 1
-- and HT.METRIC_CRITICAL = 1 --uncomment this line if you want to restrict the results to critical Quality rules
join DSS_METRIC_RESULTS R
on R.METRIC_ID = HT.METRIC_ID + 1
and R.METRIC_VALUE_INDEX = 1
and R.SNAPSHOT_ID = HT.SNAPSHOT_ID
join DSS_LINK_INFO DLI
on DLI.SNAPSHOT_ID = HT.SNAPSHOT_ID
and DLI.LINK_TYPE_ID = 3
and DLI.NEXT_OBJECT_ID = R.OBJECT_ID
join DSS_LINK_INFO DLIM
on DLIM.SNAPSHOT_ID = HT.SNAPSHOT_ID
and DLIM.LINK_TYPE_ID = 1
and DLIM.NEXT_OBJECT_ID = DLI.PREVIOUS_OBJECT_ID
join (select *
from DSS_OBJECTS dsom
where dsom.OBJECT_TYPE_ID = 20000
) dsom
on dsom.OBJECT_ID = dlim.PREVIOUS_OBJECT_ID
join DSS_OBJECTS dsot
on dsot.OBJECT_ID = dli.PREVIOUS_OBJECT_ID
and dsot.OBJECT_NAME not like '% union content%'
where R.SNAPSHOT_ID = 8
group by R.SNAPSHOT_ID, R.OBJECT_ID,HT.metric_id
) T
on T.OBJECT_ID = dso.OBJECT_ID
join DSS_LINK_INFO DLI
on DLI.SNAPSHOT_ID = T.SNAPSHOT_ID
and DLI.LINK_TYPE_ID = 3
and DLI.NEXT_OBJECT_ID = T.OBJECT_ID
join DSS_LINK_INFO DLIM
on DLIM.SNAPSHOT_ID = T.SNAPSHOT_ID
and DLIM.LINK_TYPE_ID = 1
and DLIM.NEXT_OBJECT_ID = DLI.PREVIOUS_OBJECT_ID
join DSS_OBJECTS dsom
on dsom.OBJECT_ID = dlim.PREVIOUS_OBJECT_ID
and dsom.OBJECT_TYPE_ID = 20000
and dsom.OBJECT_NAME not like '% union content%'
join DSS_OBJECTS dsot
on dsot.OBJECT_ID = dli.PREVIOUS_OBJECT_ID
and dsot.OBJECT_NAME not like '% union content%'
where T.SHARING_FACTOR > 1
group by t.snapshot_id, dso.object_id,dsom.OBJECT_ID,dsot.object_id
order by T.SNAPSHOT_ID, dso.OBJECT_ID, dso.OBJECT_NAME, dso.OBJECT_FULL_NAME, dsom.OBJECT_ID, dsom.OBJECT_NAME, dsot.OBJECT_ID, dsot.OBJECT_NAME
Result -
"snapshot_id";"object_id";"object_name";"object_full_name";"object_id";"object_name";"object_id";"object_name";"violations";"shared_violations"
8;110115;"findHistoGradeActuelByAgent";"RmoAfrHistoGradeDaoImpl.findHistoGradeActuelByAgent";7;"metier";163410;"metier-dao_7f76e979_";1;2
8;110159;"searchByCriteria";"RmoOdmHistoStatutOmDaoImpl.searchByCriteria";7;"metier";163410;"metier-dao_7f76e979_";1;2
Query for Oracle
Same as Query for CSS
Query result example
Same as Query for CSS
Query result interpretation
Same as Query for CSS
Query for SQL Server
Same as Query for CSS
Query result example
Same as Query for CSS
Query result interpretation
Same as Query for CSS
Notes/comments
Ticket # 9052
Related Pages
