SQL Queries - CAST Knowledge Base - Queries on PeopleSoft technology - Module size for MODIFIED objects

Created by Amine Benarbia on Feb 11, 2022


Purpose of Query

This query returns the size of modified object per type

Applicable CAST Version


Release
Yes/No
8.3.x(tick)
8.2.x(tick)
Applicable RDBMS


RDBMS
Yes/No
CSS4(tick)
CSS3(tick)
CSS2(tick)


Query for CSS
select T4.object_type,t.typdsc,count(1)
from keys T1 join setroot T2 on (T2.idset = T1.idkey) join keys T3 on (T3.idkey = T2.idroot) join ctt_object_applications T4 on (T4.application_id = T2.idroot)
join typ t 
on t.idtyp = object_type
where T1.objtyp = 20000 and T1.keynam = 'REPOSITORY_MODIFIED'
and T3.keynam not like '%PeopleSoft(Objects list)' and T4.properties = 0
group by T4.object_type, t.typdsc
union all
select T4.object_type,t.typdsc,count(1)
from keys T1 join setroot T2 on (T2.idset = T1.idkey) join keys T3 on (T3.idkey = T2.idroot) join ctt_object_applications T4 on (T4.application_id = T2.idroot)
join typ t 
on t.idtyp = object_type
where T1.objtyp = 20000 and T1.keynam = 'REPOSITORY_MODIFIED'
and T3.keynam like '%PeopleSoft(Objects list)' and T4.properties = 0 and T4.object_type in (1600673,1600999)
group by T4.object_type, t.typdsc order by 3 desc
Query result example
1600008;"PeopleSoft Display Type";42
1600004;"PeopleSoft Subset";1
1600006;"PeopleSoft Environment";1
1600007;"PeopleSoft Module";1
Query result interpretation
The query returns the id of the type, the name of of the type and count of object per type
Notes/comments



Related Pages