SQL Queries - CAST Management Base - Query to delete module that has no analysis unit tagged to it

Purpose of Query

This script can be used to delete the modules that have no analysis unit tagged to them, hence empty module.

Applicable CAST Version

Applicable RDBMS

Query for JAVA (CSS, Oracle, SQL Server)

Script attached. server_FunctionalModuleCleanup.sql

• To run the script for only one application -

1. Launch the create function - MAINT_ModulesCleanup from the script attached.
2. Run - select * from MAINT_ModulesCleanup( <app_local schema name>)
   drop function MAINT_ModulesCleanup( I_schema varchar(100));

• To run the script for all the applications on the one DB server -

1. Launch functions MAINT_FunctionalModuleCleanupOnAllApplications and MAINT_ModulesCleanup into any existing schema.
2. – launch the fix by executing:

select * from MAINT_FunctionalModuleCleanupOnAllApplications();

– drop functions
drop function MAINT_FunctionalModuleCleanupOnAllApplications();
drop function MAINT_ModulesCleanup( I_schema varchar(100));

Query result example

"schemaname","deletedrowsnb"

"additional_mngt",0

"address",10

"addresslookup_mngt",23

"addresslookupwithexcel_mngt",0

Query result interpretation

Schema name is the application on which the script was run.

deletedrowsnb is the number of rows the script deleted to remove the module.

Notes/comments

Ticket # 38162